Sum column if multiple criteria are met in adjacent cells
 

I have a table like this:

Column_A Column_B Column_C
03 S 100
03 T 100
02 S 100
02 T 100
04 S 100
04 T 100

I want to sum the cells in C only when A="03" and B="S". In this case, I
would expect the total to be 100.

Assume your values are in the range A1:A10, B1:B10 and C1:C10:

=SUMPRODUCT(--(A1:A10="03"),--(B1:B10="S"),C1:C10)
--
Regards,
Dave


"GateKeeper" wrote:

I have a table like this:

Column_A Column_B Column_C
03 S 100
03 T 100
02 S 100
02 T 100
04 S 100
04 T 100

I want to sum the cells in C only when A="03" and B="S". In this case, I
would expect the total to be 100.

Perfect. What is the purpose of the "--"?
--
GateKeeper
Experienced Computer User


"David Billigmeier" wrote:

Assume your values are in the range A1:A10, B1:B10 and C1:C10:

=SUMPRODUCT(--(A1:A10="03"),--(B1:B10="S"),C1:C10)
--
Regards,
Dave


"GateKeeper" wrote:

I have a table like this:

Column_A Column_B Column_C
03 S 100
03 T 100
02 S 100
02 T 100
04 S 100
04 T 100

I want to sum the cells in C only when A="03" and B="S". In this case, I
would expect the total to be 100.

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GateKeeper" wrote in message
...
Perfect. What is the purpose of the "--"?
--
GateKeeper
Experienced Computer User


"David Billigmeier" wrote:

Assume your values are in the range A1:A10, B1:B10 and C1:C10:

=SUMPRODUCT(--(A1:A10="03"),--(B1:B10="S"),C1:C10)
--
Regards,
Dave


"GateKeeper" wrote:

I have a table like this:

Column_A Column_B Column_C
03 S 100
03 T 100
02 S 100
02 T 100
04 S 100
04 T 100

I want to sum the cells in C only when A="03" and B="S". In this
case, I
would expect the total to be 100.

This would work just as well:

=SUMPRODUCT((A1:A10="03")*(B1:B10="S")*C1:C10)

But to explain the unary, check these out:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

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

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"GateKeeper" wrote in message
...
Perfect. What is the purpose of the "--"?
--
GateKeeper
Experienced Computer User


"David Billigmeier" wrote:

Assume your values are in the range A1:A10, B1:B10 and C1:C10:

=SUMPRODUCT(--(A1:A10="03"),--(B1:B10="S"),C1:C10)
--
Regards,
Dave


"GateKeeper" wrote:

I have a table like this:

Column_A Column_B Column_C
03 S 100
03 T 100
02 S 100
02 T 100
04 S 100
04 T 100

I want to sum the cells in C only when A="03" and B="S". In this
case, I
would expect the total to be 100.

Hi,

Try this with array formulas (Ctrl+Shift+Enter):

SUM(IF((A5:A7="03")*(B5:B7="A"),C5:C7))

Regards,

Ashish Mathur



"GateKeeper" wrote:

I have a table like this:

Column_A Column_B Column_C
03 S 100
03 T 100
02 S 100
02 T 100
04 S 100
04 T 100

I want to sum the cells in C only when A="03" and B="S". In this case, I
would expect the total to be 100.