sum a range after multiple criteria

[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

 

[Ragdyer]

Sounds like you'll need something like the Sumproduct() function.
The XL help files on this function are really *no* help.

Check out this link and then post back with any further questions:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Roger Govier]

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.

 

[Guest]

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]

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

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

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.

 

[Guest]

I was having a similar issue and your link was very helpful and easy to follow.
Thanks,
Tulio

 

[Ragdyer]

If this post was aimed at me, simply the messenger, your thanks should go to
the author of that site, Bob Phillips.