Squeezing more conditions into a sample SUMPRODUCT formula - show a novice pls!

[RogerRangeRover]

Hi Wizards!

I have the following formula:

=SUMPRODUCT((NINOSyNINAS!D4:D5000="F")*(((NINOSyNINAS!GA4:GA5000<=TPRIII!A11)*(NINOSyNINAS!GA4:GA5000>=TPRIII!A10))+((NINOSyNINAS!GB4:GB5000<=TPRIII!A11)*(NINOSyNINAS!GB4:GB5000>=TPRIII!A10))))

What I would need to do is to add the following condition to this formula:

Only count IF

NINOSyNINAS!BP5:BP5000 OR
NINOSyNINAS!BQ5:BQ5000 OR
NINOSyNINAS!BR5:BR5000 OR
NINOSyNINAS!DG5:DG5000 OR
NINOSyNINAS!DH5:DH5000 OR
NINOSyNINAS!DI5:DI5000 OR
NINOSyNINAS!EX5:EX5000 OR
NINOSyNINAS!EY5:EY5000 OR
NINOSyNINAS!EZ5:EZ5000 OR

is not blank.

I figured the new condition would look like this:

((NINOSyNINAS!BP:BP5000<>"")+(NINOSyNINAS!BQ:BQ5000<>"")+(NINOSyNINAS!DG:DG5000<>"")+(NINOSyNINAS!DH:DH5000<>"")+(NINOSyNINAS!DI:DI5000<>"")+(NINOSyNINAS!EX:EX5000<>"")+(NINOSyNINAS!EY:EY5000<>"")+(NINOSyNINAS!EZ:EZ5000<>""))

But how do I fit it in the main formula?

I tried:

=SUMPRODUCT((NINOSyNINAS!D4:D5000="F")*(((NINOSyNINAS!GA4:GA5000<=TPRIII!A11)*(NINOSyNINAS!GA4:GA5000>=TPRIII!A10))+((NINOSyNINAS!GB4:GB5000<=TPRIII!A11)*(NINOSyNINAS!GB4:GB5000>=TPRIII!A10))))*((NINOSyNINAS!BP:BP5000<>"")+(NINOSyNINAS!BQ:BQ5000<>"")+(NINOSyNINAS!DG:DG5000<>"")+(NINOSyNINAS!DH:DH5000<>"")+(NINOSyNINAS!DI:DI5000<>"")+(NINOSyNINAS!EX:EX5000<>"")+(NINOSyNINAS!EY:EY5000<>"")+(NINOSyNINAS!EZ:EZ5000<>""))

But it does not work...

Thank you for your help!!!

[Richard Buttrey]

Hi,

What are you trying to do? Find a particular value, or sum (or count) a range of values, given a lot of IF conditions?
It's possible that a SUMIFS() or COUNTIFS() formula (given you have Excel 2010) will be preferable to the old SUMPRODUCT work around that was necessary in earlier versions.

[RogerRangeRover]

Hi Richard,

Thank you. I´m making a reporting table. Basically it entails counting alot of values meeting different combinations of conditions. For example count only if one column is F when columns A,B or C has anything entered in it and when the entry is for a date falling between two dates etc. So it is basically counting with alot of IFs.

I suppose COUNTIFS would better suit my purpose? Would you know the most concise reference on how to use it with multiple conditions like AND OR NOT?

Many thanks,

Roger

[Miraun]

Would a pivot table not be an easier solution?

[Richard Buttrey]

Hi

Yes a COUNTIFS() function would be infinitely preferable and with a big database far more efficient.

See if the attached gives you some ideas. Note that I've added a helper column F to test whether at least one of columns A,B or C contains a value. The default standard for a COUNTIFS() function is the AND operator. So without a helper column you would need to add three COUNTIFS() together. i.e.

=COUNTIFS()+COUNTIFS()+COUNTIFS()

using columns A, B & C respectively in each one.


Or of course if your layout lends itself a PivotTable as suggested by Miraun whilst I was typing!

[RogerRangeRover]

Thanks for the tip Miraun, I´ll look into it!

[RogerRangeRover]

This is extremely helpful Richard, much appreciated.

[RogerRangeRover]

Hi

Yes a COUNTIFS() function would be infinitely preferable and with a big database far more efficient.

See if the attached gives you some ideas. Note that I've added a helper column F to test whether at least one of columns A,B or C contains a value. The default standard for a COUNTIFS() function is the AND operator. So without a helper column you would need to add three COUNTIFS() together. i.e.

=COUNTIFS()+COUNTIFS()+COUNTIFS()

using columns A, B & C respectively in each one.


Or of course if your layout lends itself a PivotTable as suggested by Miraun whilst I was typing!

Hi Richard,

Thanks again for your example with solutions.


If you kindly have a look at the attacment with the simple table.

If I am using the COUNTIFS function only, what could the formulas be for the following:

1. Number of records where A is "F", B or C or D is not blank, and E date falls between start and end dates?
2. Number of records where A is "M", B and C and D is blank and G or H date falls between start and end date?


One more question please. Since I have dozens of combintations like these that I need to have for an expansive reports table (which is always used as it is - same data is always looked for), how could I work with the COUNTIFS function in terms of adding one condition next to the other e.g.

COUNTIFS(range,criteria) when COUNTIFS(range,criteria) but not when COUNTIFS(range,criteria) etc. What signs are used for example "when" "and" "or" in the formula itself?

Million thanks for your help.

[Richard Buttrey]

Hi,

See attached

In answer to your last Q. as I mentioned last time the default situation in a COUNTIFS() function is the AND condition. i.e. all criteria must be true or the count will be zero.

If you need an OR condition to apply then you need to concatenate two or more Countifs together. i.e. =COUNTIF()+COUNTIF() so that if a criteria is not met in the first but is met in the second, you'll add a zero plus a count.

You can see with your second question that although you have an OR condition with the G&H dates, that I have overcome this by using a helper column A to get a test date into a single column. This allows you to avoid using two (or more) COUNTIFA added together. I often prefer this technique of using a helper columns since it makes the formula smaller and easier to read.

The signs are as in these example, i.e. the normal logical operators >, >=, <, <= <>

[RogerRangeRover]

Hi Richard,

Thank you very much indeed! I think I will be able to go forward now. I´ve just needed to learn this much from scratch. But with your example answers with clear expalanations I should be able to get the job done. Life does seem simpler with the helper columns. Thanks again for all your precious help, RRR