MrExcel Publishing
Your One Stop for Excel Tips & Solutions

What's wrong with my formula?

Posted by Yendor on April 07, 2000 11:36 AM

Trying to count the number of rows that have E?/RA* in the first column, and a non-blank cell in the second column.


This always gives the same (wrong) value! Tried changing the search values, but still get the same (wrong) results! Can anyone tell me a better way to do this? It has to be a single step process.

Posted by AB on April 07, 2000 1:30 PM

=SUM( (A81:A135="E?/RA*") * NOT(ISBLANK(B81:B135)) )

Posted by Yendor on April 10, 2000 11:04 AM

Thank you for your help, but it didn't work. Wildcards aren't allowed in an = operation. Finally came up with this:

It works, even if it's a bit clunky. Perhaps what I should do is teach these people how to enter accurate data into the forms, huh?

Posted by AB on April 11, 2000 6:14 AM

Silly me...
I thought it was a literal search :)

How bout this then? (a tad more complex)

=SUM( IF(NOT(ISERROR(SEARCH("E?/RA*", A81:A135))),1) * NOT(ISBLANK(B81:B135)) )