sum with multiple criteria

  • Thread starter Thread starter Gecko
  • Start date Start date
G

Gecko

Hi,

I need to make the sum of the values from a customer in a certain month.

month 1 month 2 month 3
customer1
customer2
customer3
customer1
customer2
customer3

Can anyone give the right formula ? I already tried with Sumif and Sum, but
nothing worked so far...

Thanks, and happy New Year to you all !!!
 
Forgot some important info.

The 'original' data are in colums. Colum A is the Month, Colum B is the
customer, colum X is the value.

The final result should look like this:

month 1 month 2 month 3
customer1
customer2
customer3



Thanks.
 
With months in A1:A1000, names in B1:B1000, numbers in X1:X1000
With your table beginning with first name in Z2 and first month in AA1

=SUMPRODUCT(--($A$1000=AA$1),--($B$1:$B$100=$Z2),$X1:$X1000)

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct

If column A had dates not months
=SUMPRODUCT(--(month($A$1000)=AA$1),--($B$1:$B$100=$Z2),$X1:$X1000)

If you are using Excel207, have a look in Help to lean about SUMIFS (with
final S)
best wishes
 
Hi,
I assume that the original data is in sheet 1 and the summary you need in
sheet 2
sheet 2 column A you have the customer name starting in A2 and in B1 you
have the month so in B2 enter

=sumproduct((A1=sheet1!$B$1:$B$10000),(B1=sheet1!$A$1:$A$10000),$X$1:$X$10000
 
opps, use this one instead

=SUMPRODUCT(($A2=Sheet1!$B$1:$B$10000),(C$1=Sheet1!$A$1:$A$10000),$X$1:$X$10000)

copy formula to your right and down
 
Back
Top