Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | (show all)   Print Thread
Standard User daveb86
(newbie) Tue 17-Apr-12 11:04:08
Print Post

Formula Help please


[link to this post]
 
Hi Guys

Im looking for help with the following formula

=IF(B:B="Text",count(E:E))

For Example: Column B has different words in eg: Monday, Tuesday, Wednesday etc
And Column E has different text in eg: Yes, No and Empty

I want to be able to count all the text data in column E that appears with each Day

So i have 5 rows containing Monday in Column B but each row contains Yes Yes No No Empty

I want it to count all 5 regardless of what the text in Column E says

Thanks

Standard User XRaySpeX
(eat-sleep-adslguide) Tue 17-Apr-12 11:33:16
Print Post

Re: Formula Help please


[re: daveb86] [link to this post]
 
Isn't this similar to http://forums.thinkbroadband.com/windows/t/4087807-s... ?

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Standard User daveb86
(newbie) Tue 17-Apr-12 11:39:34
Print Post

Re: Formula Help please


[re: XRaySpeX] [link to this post]
 
In reply to a post by XRaySpeX:
Isn't this similar to http://forums.thinkbroadband.com/windows/t/4087807-s... ?


Nope as im try to use counts text not numbers smile

This is a task ive been set at work i can figure out most formula's but this one seems to stump me for some reason

If anyone can provide me the formula that would be excellent smile


Register (or login) on our website and you will not see this ad.

Standard User XRaySpeX
(eat-sleep-adslguide) Tue 17-Apr-12 13:07:17
Print Post

Re: Formula Help please


[re: daveb86] [link to this post]
 
In reply to a post by daveb86:
I want to be able to count all the text data in column E that appears with each Day
Ask a silly Q, but how do you count text? Do you mean how many "Yes"es there are?

In your example is the answer:
  • 5? You said "regardless of what the text in Column E says". So Col E is irrelevant?
  • 2? The no. of "yes"es?
  • 4? The no. of non-empty ones?
  • or what?


1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Standard User ian72
(knowledge is power) Tue 17-Apr-12 13:09:55
Print Post

Re: Formula Help please


[re: daveb86] [link to this post]
 
Can you expand on your description as it isn't clear to me what you are attempting?

If you just wanted to count all of the entries in column B that say Monday then you could do a

=countif(A:A, "Monday")

If you want more advanced than that (ie all of the counts of "Yes" in column E for entries of Monday in column B) then maybe a pivottable would be better for you?
Standard User XRaySpeX
(eat-sleep-adslguide) Tue 17-Apr-12 13:17:18
Print Post

Re: Formula Help please


[re: ian72] [link to this post]
 
In reply to a post by ian72:
=countif(A:A, "Monday")
LOL! Just came to that!

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Standard User daveb86
(newbie) Tue 17-Apr-12 13:20:06
Print Post

Re: Formula Help please


[re: XRaySpeX] [link to this post]
 
In reply to a post by XRaySpeX:
In reply to a post by daveb86:
I want to be able to count all the text data in column E that appears with each Day
Ask a silly Q, but how do you count text? Do you mean how many "Yes"es there are?

In your example is the answer:
  • 5? You said "regardless of what the text in Column E says". So Col E is irrelevant?
  • 2? The no. of "yes"es?
  • 4? The no. of non-empty ones?
  • or what?


So if Column B says Monday but Column E is not blank then i want it to count it for monday then im going to repeat this formula for each day smile

Standard User ian72
(knowledge is power) Tue 17-Apr-12 13:27:37
Print Post

Re: Formula Help please


[re: daveb86] [link to this post]
 
I think you could only do that via a macro or via a pivottable. I don't believe you can count based on the values in 2 columns using formulas.
Standard User XRaySpeX
(eat-sleep-adslguide) Tue 17-Apr-12 13:28:39
Print Post

Re: Formula Help please


[re: daveb86] [link to this post]
 
Clarify! Is the answer to your example in OP = 4? The no. of non-blank entries in col E against col B = "Monday"? Be specific and we might have an idea of what you are getting at.

You never mentioned at all that you wanted to count non-empty cells; just that you wanted to "count text" frown. Hence my querying what you meant by this.

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Standard User ian72
(knowledge is power) Tue 17-Apr-12 13:51:49
Print Post

Re: Formula Help please


[re: ian72] [link to this post]
 
OK, so it looks like it can be done using arrays and sum.

If I have a spreadsheet with column A containing days and column B containing Yes, No or blanks then the following formula will count all Mondays with a non-blank in B

=SUM((A:A="Monday")*(B:B<>""))

However, this only works if when entering the formula you use Ctrl-Shift-Enter rather than just enter (this will add curly brackets around the formula making it into an array formula).

Clever stuff this Excel.
Pages in this thread: 1 | 2 | (show all)   Print Thread

Jump to