Formula Help please

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

Isn't this similar to http://forums.thinkbroadband.com/windows/t/4087807-s... ?

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

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

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?

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?

In reply to a post by ian72:

=countif(A:A, "Monday")

LOL! Just came to that!

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

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.

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" . Hence my querying what you meant by this.

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.