Array formula to count occurrances with three conditions.

saracz66

New Member
Joined
Jan 23, 2012
Messages
16
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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))}
 
Upvote 0
Perhaps the values in column C are not truely blank, are they formulas returning "" ?

Try
(Data!C:C="")
instead of
ISBLANK(Data!C:C)
 
Upvote 0
That was it! Something so simple I should have thought of it. Thanks for leading me in the right direction!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top