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

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

This is extremely helpful Richard, much appreciated.

Originally Posted by 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!

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.