Array formula to count occurrances with three conditions.

[saracz66]

Hello,

I have the following array formula:

{=SUM(IF(ISNUMBER(FIND(A37,Data!G:G,1)),IF(ISBLANK(Data!C:C),IF(ISNUMBER(FIND("MSP",UPPER(Data!B:B),1)),0,1),0),0))}

In layman's terms it says: count the number of occurances where the text in cell A37 is found in column G, a BLANK is in column C and "MSP is not found in column B (1 assigned to FALSE position in 3rd IF statement).

The function only partially works. I have 8 occurances of the scenario above but the formula is only returning a count of 5. I've checked my data but cannot pinpoint the reason for why the formula is returning 5 and not 8.

Any help would be greatly appreciated.

 

[Jonmo1]

One possible reason is that FIND is case sensitive.
If the A37 = "hello", but the cells in column G are "Hello", then it won't find it.

Change FIND to Search

And the opposite of ISNUMBER would be ISERROR, so you don't need to do the 1 for false and 0 for true conversion.

 

[saracz66]

Thanks for your quick feed back. Unfortunately your suggestions give me the same results. Any other thoughts?

{=SUM(IF(ISNUMBER(SEARCH(A37,Data!G:G,1)),IF(ISBLANK(Data!C:C),IF(ISERROR(SEARCH("MSP",UPPER(Data!B:B),1)),1,0),0),0))}

 

[Jonmo1]

Perhaps the values in column C are not truely blank, are they formulas returning "" ?

Try
(Data!C:C="")
instead of
ISBLANK(Data!C:C)

 

[saracz66]

That was it! Something so simple I should have thought of it. Thanks for leading me in the right direction!

 

[Jonmo1]

Glad to help, thanks for the feedback.

FYI..
Even though XL2007+ allows you to use entire column references like C:C in an array formula..
It is still not recommended to do so, it forces xl to do many unnecessary calculations in unused ranges.
It's almost always best to use specific ranges like C1:C1000