Wrong Result

[gymwrecker]

Need help with this formula.

=SUMPRODUCT(ISNUMBER(SEARCH("*0070*",Master!$A$2:$A$1000))*ISNUMBER(SEARCH("*OD*",Master!$K$2:$K$1000)))

The above formula is returning a result of 1; I counted the cells that meet the above criteria, and the correct number is 3. What could be wrong? Seems to me that the formula is not counting everything. Please assist....

 

[shg]

Where do the *0070* numbers come from? If the leading numbers come from formatting numeric cells, they won't be seen by the formula.

 

[gymwrecker]

Should I format all the cells as "numbers"?

 

[shg]

What are some examples of the data in col A?

 

[gymwrecker]

Column A has the following; around 600 rows...

0084 TRAINING COMMAND (UR)
0070 TRAINING DIV HQ (-) (FT)
0078 TRAINING DIV (OPERATIONS)
0086 TRAINING DIV (OPERATIONS)
0091 TRAINING DIV (OPERATIONS)

 

[shg]

That should work fine.

I'd suggest you reduce the data ranges to just a few rows, and then watch the formula evaluate with the Evaluate button on the formula auditing toolbar.