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?

 

[Aladin Akyurek]

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?

By resorting to somewhat costly multiplications...

Suppose we have

=MAX(IF(RangeX=X,IF(RangeY=Y,RangeZ)))

re-wriiten:

=MAX(IF((RangeX=X)*(RangeY=Y),RangeZ))

 

[spb]

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.

New problem:
If my data NE_Priced has values, but my comparator (P12) is empty, it returns a False. The formula above looks like it should work. If the IF nested inside the IF is true the data matches the data which should be a one for one count, if false the data that matches the drop down selection (P12) is counted.
Any ideas. All the data being compared is text, does that matter?

 

[Aladin Akyurek]

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.

New problem:
If my data NE_Priced has values, but my comparator (P12) is empty, it returns a False. The formula above looks like it should work. If the IF nested inside the IF is true the data matches the data which should be a one for one count, if false the data that matches the drop down selection (P12) is counted.
Any ideas. All the data being compared is text, does that matter?

I don't think I'm getting the new problem. Here is a set up that is compatible with your original query...

Book4
	A	B	C	D	E	F	G	H	I
1	X	Y	Z		Conditions				Count
2	1	a	south		1	3		south	3
3	3	a	north		1	3	b	south	2
4	2	b	south
5	4	b	south
6	3	c	north
7	5	a	north
8	2	b	south
9	2	a	north
Sheet1

E:F houses the between conditions the range in A must meet.

G houses a choice: no condition (empty) or a value like b.

H houses a region of interest.

In I, we have the formula for counting:

=SUMPRODUCT(--($A$2:$A$9>=E2),--($A$2:$A$9<=F2),--($B$2:$B$9=IF(G2="",$B$2:$B$9,G2)),--($C$2:$C$9=H2))

Would you describe the new problem in reference to the above exhibit?

 

[spb]

In relation to the above I can't see a difference. Using your example, if G2 is blank, and B2:B9 are fully populated I get false return. Looking at the formula, G2 should be ignored and in your example it is, in mine it P14) isn't. P14 empty, data full, false return.
Here's my formula:

=SUMPRODUCT(--(NE_Generated>=$P$11),--(NE_Generated<=$P$12),--(NE_Region=C$3),--(NE_Priced=IF($P$14="",NE_Priced,$P$14)),--(NE_NIPR=IF($P$15="",NE_NIPR,$P$15)),--(NE_CA=IF($P$16="",NE_CA,$P$16)),--(NE_Labor=IF($P$17="",NE_Labor,$P$17)))

My problem is if P14:P17 are empty "", but NE_priced and the other three ranges compared to P15:P17 have data, I get a false return. I would like the data in NE_priced (as well as NE_NIPR, NE_CA, and NE_labor) to be ignored since P14:P17 (drop down selections to consider 2 conditions or not) was empty. Basically if P14:P17 is empty, don't factor NE_priced and the other ranges into the determination. I was to mix and match selections in P14:P17 to arrive at different outcomes.
In your example it works, in mine it doesn't.
I know in index tables that the row and col labels associated with an index have to be arranged alphabetically. Could something like that have any influence here? The data I'm comparing between the drop down selection and my data table is text, could that have any impact?
I'm scratching my head here. I double checked my cell ranges to names, cell references, anything I could think of. Sorry if I was redundant, I'm tired. Thanks for you help.

 

[Aladin Akyurek]

In relation to the above I can't see a difference. Using your example, if G2 is blank, and B2:B9 are fully populated I get false return. Looking at the formula, G2 should be ignored and in your example it is, in mine it P14) isn't. P14 empty, data full, false return.
Here's my formula:

=SUMPRODUCT(--(NE_Generated>=$P$11),--(NE_Generated<=$P$12),--(NE_Region=C$3),--(NE_Priced=IF($P$14="",NE_Priced,$P$14)),--(NE_NIPR=IF($P$15="",NE_NIPR,$P$15)),--(NE_CA=IF($P$16="",NE_CA,$P$16)),--(NE_Labor=IF($P$17="",NE_Labor,$P$17)))

My problem is if P14:P17 are empty "", but NE_priced and the other three ranges compared to P15:P17 have data, I get a false return. I would like the data in NE_priced (as well as NE_NIPR, NE_CA, and NE_labor) to be ignored since P14:P17 (drop down selections to consider 2 conditions or not) was empty. Basically if P14:P17 is empty, don't factor NE_priced and the other ranges into the determination. I was to mix and match selections in P14:P17 to arrive at different outcomes.
In your example it works, in mine it doesn't.
I know in index tables that the row and col labels associated with an index have to be arranged alphabetically. Could something like that have any influence here? The data I'm comparing between the drop down selection and my data table is text, could that have any impact?
I'm scratching my head here. I double checked my cell ranges to names, cell references, anything I could think of. Sorry if I was redundant, I'm tired. Thanks for you help.

Would you verify the following:

While P14:P17 selections are all supposed blank, what do you get with:

=COUNTIF(P14:P17,"?*")

 

[spb]

That returns a 0.

 

[spb]

Problem solved. The error I had was acyually in the SE region data. I broke down the formula one condition at a time and the error was due to the --(SE_CA=IF($P$16="",SE_NIPR,$P$16). Mixed cell range reference. Sorry for wasting your time.