How to count the number of cell in row if the cell meet criteria
Hi everyone, I cannot deal with the problem as follows:
If I want to count how many cells in row if it meets "Orange" with excel statement, how can i do it?
For example: Orange is 4, Kiwifruit is 2 etc...
A B C D E
------------------------------------
Apple a a a a
Orange b b b b
Lemon e e e e
Kiwifruit y y
perhaps
=COUNTIF(INDEX(B:E,MATCH("Orange",A:A,0),0),"?*")
Here is another option...
=SUMPRODUCT(--(A1:A5="orange")*(B1:F5<>""))
(I increased the range for testing)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
On checking, it seems the COUNTIF/Index doesnt work on an expanded range (maybe Im missing something?)
Apple a a a a Sumproduct Countif Orange b b b b b Lemon e e e e Kiwifruit y y Orange b b
Sure, COUNTIF(INDEX(MATCH returns only first single row if matched.Originally Posted by FDibbins
Quang PT
oops of course, thanks (late nite, brain not working). So, if "orange" is repeated further down, it will be ignored.
correct; of course, per OP post, there's nothing to actually indicate that's the case... ;-)
You make a valid point
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks