countif or sumproduct to count if criteria set

hi all,

i need to know how to count row if certain criteria is set as per the picture,

i want to count how many fruit in jan, feb, mar and so on if the criteria set to "fruit"
(i give the wanted answer in H3:Q3, and the criteria sets in F2 "Fruit")

if we using sumproduct..it will sum and not count so i guess we should using another formula.

thanks for helping

fruit.jpg

OP, try this:

Please Login or Register  to view this content.

for others, OP has provided a sample file in this other thread:

http://www.excelforum.com/showthread...=1#post2861028

Hi,

Try.

=SUMPRODUCT((H7:H13<>"")*($F7:$F13=$F2))

If you are on Excel 2007 or later,

=COUNTIFS(H7:H13,"<>",$F7:$F13,$F2)

"<>" means 'Is Not Blank' could be anything. Number, text, 'space' just checking cell is not blank