Ignore Logical Test if Condition Reference is Blank

spb

Board Regular
Joined
Sep 29, 2006
Messages
51
I am composing a COUNTIF formula that counts occurances of projects that fall within a specified date range and have criteria picked from drop downs that match criteria on my data source. I can write the formula if I want to consider all the criteria I pick from the four separate drop downs. My problem is that I don't want a condition considered if my drop down criteria is empty, in essence ignore that particular IF statement if the down down criteria is empty. Any Ideas?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Assuming A1 is the cell with the dropdown:

=IF(A1="","",COUNTIF(...your formula...))
 

spb

Board Regular
Joined
Sep 29, 2006
Messages
51
I think we're close. Here's my current array:

{=COUNT(IF(NE_Saved>=$P$9,IF(NE_Saved<=$P$10,IF(P12="","",IF(NE_Priced=P12,IF(NE_Region=C3,1))))))}

$P$9 and $P$10 are my date ranges. P12 is the drop down text that it matches my data source count it. C3 is a data that matches data on my data source as well. I want P12 condition to be ignored if P12 is empty. Right now when I have data in P12 that matches my data source it works, but when I empty P12, I get False, result is 0 when it should be about 70.
Can you see where I screwed up?
Thanks,
Steve
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Ah. I didn't know we were talking about an array.

I may be wrong here, but I think that what you seem to be trying to do would be easier using SUMPRODUCT. Maybe try this?

Code:
=SUMPRODUCT((NE_Saved>=$P$9)*(NE_Saved<=$P$10)*($P$12<>"")*(NE_Priced=$P$12)*(NE_Region=C3))

Also, you may want to try searching the board for threads about counting multiple conditions :)
 

spb

Board Regular
Joined
Sep 29, 2006
Messages
51

ADVERTISEMENT

Kristy,
That didn't work either. When I cleared the $P$12 the count went back to 0. I'll search the board like you said. It has to have been done before. I'm just trying to pick items from a few drop down boxes to alter the outcome. I wish I knew VB well enough to tackle this but I can't believe the formulas in excel can't do the same thing. Thanks for your help.
Steve
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
I think we're close. Here's my current array:

{=COUNT(IF(NE_Saved>=$P$9,IF(NE_Saved<=$P$10,IF(P12="","",IF(NE_Priced=P12,IF(NE_Region=C3,1))))))}

$P$9 and $P$10 are my date ranges. P12 is the drop down text that it matches my data source count it. C3 is a data that matches data on my data source as well. I want P12 condition to be ignored if P12 is empty. Right now when I have data in P12 that matches my data source it works, but when I empty P12, I get False, result is 0 when it should be about 70.
Can you see where I screwed up?
Thanks,
Steve

Better switch to something faster...

Either:

{=SUM(IF(NE_Saved>=$P$9,IF(NE_Saved<=$P$10,IF(NE_Priced=IF(P12="",NE_Priced,P12),IF(NE_Region=C3,1)))))}

Or:

=SUMPRODUCT(--(NE_Saved>=$P$9),--(NE_Saved<=$P$10),--(NE_Priced=IF(P12="",NE_Priced,P12)),--(NE_Region=C3))

which just needs enter.
 

spb

Board Regular
Joined
Sep 29, 2006
Messages
51

ADVERTISEMENT

I think this is working, awesome!! I can't thank you enough. I'll probably use the sumproduct because I have to alter the formula to account for the other three drop down conditions and I think I might exceed 7 conditions that I don't think will work with the sum if. You're a lifesaver.
Steve
 

spb

Board Regular
Joined
Sep 29, 2006
Messages
51
Can the same type of logic be applied to MIN MAX functions? I'd like to extract min max numbers (from other columns of data) using the same criteria above but don't know if I can use sumproduct type conditions to get them.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Can the same type of logic be applied to MIN MAX functions? I'd like to extract min max numbers (from other columns of data) using the same criteria above but don't know if I can use sumproduct type conditions to get them.

You'll need a formula like

MAX(IF(NE_Saved>=$P$9,IF(NE_Saved<=$P$10,IF(NE_Priced=IF(P12="",NE_Priced,P12),...

fashioned like SUM(IF(... which you need to confirm with control+shift+enter.
 

spb

Board Regular
Joined
Sep 29, 2006
Messages
51
Aladin, thanks. I did get that to work but only for up to 7 IF statements as designed. I have 10. I guess that's why I liked the sumproduct solution discussed earlier for my other problem. Any ideas on how to get around the array nesting limitation?
 

Forum statistics

Threads
1,136,613
Messages
5,676,813
Members
419,652
Latest member
jjakub33

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
Top