sum a range after multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have 4 criteria that all have to =true then sum a range. the range to find
each criterion is a column.

I have tried sumif, if(and(and(and(and, lookup, etc.

thanks,
George
 
Hi George

Sumproduct will probably provide your solution, but note that you cannot
use whole column ranges as the argument.

Use something like
=SUMPRODUCT(--($B$1:$B$65535="value1"),--($C$1:$C$65535="value2"))
You can use as many sets of conditions within the outer set of brackets
as you require.
Wrap the test in quotes "Value 1" if comparing Text, or omit the quotes
and just use numbers for Numeric data.
 
Thank you Roger. As soon as I convinced myself to use your example syntax
literally, i.e. (--( [hyphens for the number of conditions placed in the
outer brackets], then it was a snap.

George Papangellin
Fresno, California
 
Hi George

The "--" outside the condition are the double unary of minus minus.
This is used to coerce the results of the test, TRUE or FALSE into 1 or
0 so that they can be used in the Sumproduct calculation.

--
Regards

Roger Govier


George P said:
Thank you Roger. As soon as I convinced myself to use your example
syntax
literally, i.e. (--( [hyphens for the number of conditions placed in
the
outer brackets], then it was a snap.

George Papangellin
Fresno, California

Roger Govier said:
Hi George

Sumproduct will probably provide your solution, but note that you
cannot
use whole column ranges as the argument.

Use something like
=SUMPRODUCT(--($B$1:$B$65535="value1"),--($C$1:$C$65535="value2"))
You can use as many sets of conditions within the outer set of
brackets
as you require.
Wrap the test in quotes "Value 1" if comparing Text, or omit the
quotes
and just use numbers for Numeric data.
Click to expand...
Click to expand...
 
I was having a similar issue and your link was very helpful and easy to follow.
Thanks,
Tulio
 
If this post was aimed at me, simply the messenger, your thanks should go to
the author of that site, Bob Phillips.
 
Back
Top