Ignore Logical Test if Condition Reference is Blank

[spb]

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?

 

[Von Pookie]

Assuming A1 is the cell with the dropdown:

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

 

[spb]

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]

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?

=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]

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]

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]

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]

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]

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]

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?