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