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.

=COUNTA(IF(A81:A135="E?/RA*",IF(B81:B135,A81:A135)))

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:
=SUM(IF(RIGHT(LEFT(TRIM(A81:A135),5),2)="RA",IF(LEFT(TRIM(A81:A135),1)="E",IF(B81:B135<>"",1,0),0),0))

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)) )