Need a formula PLEASE!

[tammyz]

I need a formula that will count the number of times ie; apples, oranges, are listed in c26:c35 BUT will not count IF a26:a35 is blank.

Seems pretty simple but I just can't come up with it when it is looking for more than one selection in the "c" column. In other words, I used a "sumproduct" formula when just looking for say, "apples" and it seemed to work. But when I add addition criteria it won't work. H E L P !!

 

[barry houdini]

Hello tammyz, welcome to MrExcel

Try

=SUMPRODUCT((C26:C35="apples")*(A26:A35<>""))

 

[tammyz]

WOW! Thank you for the quick response!!!

I tried that formula and it does work as long as I am only looking for "apples". It just doesn't seem to work when I want it to look for multiple criteria like "count every time apples and oranges and bananas appear in this column. Appreciate it!!!

 

[Aladin Akyurek]

I need a formula that will count the number of times ie; apples, oranges, are listed in c26:c35 BUT will not count IF a26:a35 is blank.

Seems pretty simple but I just can't come up with it when it is looking for more than one selection in the "c" column. In other words, I used a "sumproduct" formula when just looking for say, "apples" and it seemed to work. But when I add addition criteria it won't work. H E L P !!

Try...

Either:

=SUMPRODUCT(--ISNUMBER(MATCH(C26:C35,{"apples","oranges"},0),--(A26:A35<>""))

Or:

=SUMPRODUCT(--ISNUMBER(MATCH(C26:C35,E2:E3,0),--(A26:A35<>""))

where E3:E3 houses the criteria of interest.

 

[barry houdini]

If you want multiple criteria try like this

=SUMPRODUCT(ISNUMBER(MATCH(C26:C35,{"apples","oranges","bananas"},0))*(A26:A35<>""))

or list all the fruit in a range, e.g. Z1:Z3 and use this version

=SUMPRODUCT(ISNUMBER(MATCH(C26:C35,Z1:Z3,0))*(A26:A35<>""))

 

[tammyz]

Thank you VERY MUCH! The first formula appears that it is going to work but the cell is displaying the formula rather than the result.

 

[Aladin Akyurek]

Thank you VERY MUCH! The first formula appears that it is going to work but the cell is displaying the formula rather than the result.

Activate Edit|Replace or Find & Select|Replace.
Set Find what to: =
Set Replace with to: =
Run Replace All.

 

[tammyz]

You guys are GREAT! THANK YOU so much!!