multiple criteria then sum

[Guest]

I am trying to figure out how to sum a column based on 2 criteria being true.

criteria1 criteria2 volume
3 2 100
2 2 500
2 1 600
3 2 200

For example, I want the sum for volume when criteria 1 = 3 and criteria 2 =
2 simultanously. In this example, volume would equal 300.

Any suggestions?

 

[Guest]

Hi

One option is to use SUMPRODUCT. There is some guidance here:
http://www.contextures.com/xlFunctions01.html#SumProduct

In your example, something like:
=SUMPRODUCT((A2:A10=3)*(B2:B10=2),C2:C10)
When using this function, each range must contain the same number of cells
and cannot be full columns.

Andy.

 

[Guest]

What if you have two criteria and want to sum the same column?
I tried
=SUMPRODUCT((A2:A10=3)*(B2:B10=2),B2:B10)
and got a VALUE error

 

[Guest]

Hi

I've just tried it and it worked for me!! Make sure that your 'numbers' are
actually numbers and not text.

Andy.

 

[Guest]

Many thanks Andy! I tried it and it works nicely. I will check out the link
as well....

Regards, Kit

 

[Guest]

Thanks for the feedback!!

Andy.

Many thanks Andy! I tried it and it works nicely. I will check out the
link
as well....

Regards, Kit