sum total base on 2 criterias

Y

yann

Hi all

I currently have an excel spreadsheet with 3 columns. Namely, Date, Name and Amount. I would like to sum up the total paid by each person every month. Is there anyway to do this using excel worksheet functions

Please refer to the following sample data for an example of what I am trying to do

Date Name Amoun
12-Jan-04 Tanya 480.5
14-Jan-04 Simon 500.0
24-Jan-04 Tanya 16.2
1-Feb-04 Clement 452.5
10-Feb-04 Tanya 12.5
....
The results I would like to get is as follo
Month Name Total Amoun
January Clement 0.0
January Simon 500.00
January Tanya 496.7
February Clement 452.5
February Simon 0.00
February Tanya 12.5

Thanks
yann
 
N

Norman Harker

Hi yann!

Try:
=SUMPRODUCT(--(TEXT($A$2:$A$6,"mmmm")=A10),--($B$2:$B$6=B10),$C$2:$C$6)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
Y

yann

Hi Norman

Thanks for your prompt reply. I tried your method, but I am getting a #NUM! error. Any idea why

Thanks agai
yann
 
Y

yann

Hi Norman

I've got it to work. Previously i was using the range i specified is the whole column $A:$A. Now i've specified the range of $A$1:$A1$00 and it works
May I ask what's does "--" in the formula mean/does

Thanks you v. much
yann
 
N

Norman Harker

Hi Yann!

The double negative has the effect of coercing returns of TRUE and
FALSE to 1 and 0.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
Top