Sumif, sumifs, counta, countifs??

[1.zer0]

I have the following table:

COL A COL B
Blue 5
Red 3
Yellow 2
Green 1
Yellow 1
Red 4
Yellow
Blue 6
Purple 3

I need to count the number of times COL B has a number or data in it (any number it doesn't matter) if COL A has the word Yellow next to it. So in my example above, Yellow would result in 2, Purple 1, Blue 2, etc. I cant work out how to do this, i think it needs some sort of nested SUMIF, COUNTA formula but cant work out what?

Many Thanks

[Richard Buttrey]

Hi

In C1 copied down

=COUNTIFS($B$1:$B$9,">0",$A$1:$A$9,A1)

Regards

[reachhari]

Hi,

Please find attached workbook which has the formula for you, I have used array formula so in order to workout that formula you have to hit CTRL+SHIFT+ENTER after you typed the formula or if you edit anything in the formula.

Thanks,

[1.zer0]

Thanks for the help, I tried richards first and it works fine. I think i was a victim of over thinking the problem!