conditional sums

  • Thread starter Thread starter JOhn
  • Start date Start date
J

JOhn

Hi,

I know I can use the sumif function if i need to sum
certain cells in a column conditionally. My problem is how
do i conditionally sum up a column using more than 1
condition? I have tried messing around with the sumif
function but in the criteria part of the function it only
seems to work when i put in 1 condition. I am trying to
take the conditions from 2 different columns, so I could
be referencing the columns wrongly. Any help would be
appreciated.

Thanks in advance
John
 
Hi John

Sumif / Countif can't. Here's a way, summing D with Sumproduct and three
criterias for A, B and C:

=SUMPRODUCT((A1:A1000="E")*(B1:B1000<>"Q")*(C1:C1000<5)*(D1:D1000))

Have also a look at Pivot tables for data like this. They group and sum
everything very neatly.
http://www.cpearson.com/excel/pivots.htm
 
Hi John
SUMIF only summports one condition. Try SUMPRODUCT instead
e.g.
=SUMPRODUCT((A1:A100="some text")*(B1:B100="other text"),C1:C100)

sums column C based on the conditions for column a and B. You can
replace this 'hardcoded' conditions with a cell reference. e.g.
=SUMPRODUCT((A1:A100=D1)*(B1:B100=E1),C1:C100)
 
Back
Top