sum with multiple criteria

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 !!!
 
G

Gecko

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.
 
B

Bernard Liengme

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
 
E

Eduardo

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
 
E

Eduardo

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top