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.

Clever!

But the A:A column form did not work for me; I had to use a range:
=SUM((A1:A9="Monday")*(B1:B9<>"")) within {}.

Just checking, you didn't have the formula in one of the 2 columns did you? If you do then it creates a recursive lookup doing the A:A and so won't work. Putting it in a separate column works on Office 2010.

No, I had it in Col E.

But I'm still using Excel 95 so it might not be so "facility-rich", but it has most main things.

Maybe 95 doesn't support the ranges that way - have to say I normally use a fixed range anyway and only reason I did it this way was because that was how the OP started out. Even so, it is a very cool function of Excel that may come in useful in future (I usually only find these things when someone asks for something a little off the wall).

Excel 95 does support column range in things like VLOOKUP where a range is expected, but not in this array formula, probably cuz a range does not normally appear in an IF = test. Have found other minor quirks like this but they are usually easily surmountable.

I think this is best done with a pivot table as mentioned. (if I understand the problem)

see an example here

Already solved by ian72.