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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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