Articles

Calculate how much expend monthy by person

December 6, 2012 at 13:27:34
Specs: Windows XP

I post this question yesterday and DerbyDad03, answer me: Read the Manual, before I contact the forum I expend several hours trying to resolve this. Please somebody can help me with this.
Thanks

The formula I need is :
Calculate how much Luis and Andrea expend by month.

Date 	Name	Amount
9/1/1994	Luis	266.03
9/1/1994	Luis	233.39
3/23/1998	Luis	75.85
3/23/1998	Andrea	410.67
3/23/1998	Luis	385.50
3/23/1998	Andrea	69.85
4/23/1998	Andrea	51.85
4/23/1998	Luis	245.39

And these are the results I need:

Month	Luis	Andrea
March	461.35	480.52
April	245.39	51.85
Septemb	499.42	0

Thanks I really appreciate any help


See More: Calculate how much expend monthy by person

Report •


#1
December 6, 2012 at 14:05:48

DerbyDad03 gave you the correct answer last time.

He did not simply state to "Read The Manual", he told you exactly what functions to lookup. The ones he specified, (SUMIF and SUMIFS) are exactly what you would need to do what you want to do.

A quick look at those functions, without even looking at the detailed help file, would show you exactly what input those functions need and how to use them to do what you want to do.

I fail to see where the confusion lies or why you would need to post another question instead of simply asking for clarification, if needed, on your previous thread. If you check out those 2 functions, and still cannot figure out what you need to do, then post a reply back here and let us know. In addition, also let us know what version of Excel you are using so that we can tailor our answers accordingly.

-----
IT Desktop & Network Consultant - MOS Master Certified, MCP, MCSA, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +

::geek::


Report •

#2
December 6, 2012 at 15:55:59

You can also use a =SUMPRODUCT()

But your first problem is going to be with your DATES.

In cells A2 - A9 you use a standard Date Format,
but in your summary section you use a TEXT String,
IE: March, April, September.

The simplest way, using a =SUMPRODUCT() function
would be with your data like:

       A         B         C
 1)   Date      Name     Amount
 2) 9/1/1994    Luis     266.03
 3) 9/1/1994    Luis     233.39
 4) 3/23/1998   Luis     75.85
 5) 3/23/1998   Andrea   410.67
 6) 3/23/1998   Luis     385.5
 7) 3/23/1998   Andrea   69.85
 8) 4/23/1998   Andrea   51.85
 9) 4/23/1998   Luis     245.39
10)                 
11)                 
12) Month       Luis     Andrea
13) March       461.35   480.52
14) April       245.39   51.85
15) September   499.42   0

In Cell B13 enter the formula:

=SUMPRODUCT(--(TEXT($A$2:$A$9,"mmmm")=$A13),--($B$2:$B$9=B$12), $C$2:$C$9)

Drag Down two rows
Drag Across one column

This should get you your totals.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#3
December 6, 2012 at 19:10:16

Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 7, 2012 at 05:17:38

“We learn by example and by direct experience because there are real limits to the adequacy of verbal instruction. ”

Malcolm Gladwell

MIKE

http://www.skeptic.com/


Report •

#5
December 7, 2012 at 11:17:02

Thanks, I'm using Excel 2007, I followed your instructions but I failed to make the formula, I make the calculations that I need separately, but when I put together in SUMIFS, I get an error I try to do it using the Formula Wizard and not let me.

This formula calculates the amount of all names:
= SUMIFS ($ K $ 4: $ K $ 14, $ B $ 4: $ B $ 14, "Luis")

and this one calculates the March AMOUNT
= SUMPRODUCT (- (MONTH ($ A $ 4: $ A $ 14) = 3), ($ K $ 4: $ K $ 14))

My problem is I can not find the way to bring these two in SUMIFS

I need Calculate the Amount of Luis in March

Thanks again for the help, and my apologies for misunderstand your post

Jose


Report •

#6
December 7, 2012 at 12:06:40

One possible solution is to insert a "helper column" to extract the Month from the dates in Column A. (You can hide this column if you don't want to see it)

Once that is done, various forms of a SUMIFS formula will work just fine.

For example, for March (Month = 3) and "Luis", this formula returned 461.35 from the table below.

=SUMIFS(D2:D9,B2:B9,3,C2:C9,"Luis")

       A          B         C         D
 1)   Date     Month Num   Name     Amount
 2) 9/1/1994   =MONTH(A2)  Luis     266.03
 3) 9/1/1994   =MONTH(A3)  Luis     233.39
 4) 3/23/1998    etc.      Luis     75.85
 5) 3/23/1998              Andrea   410.67
 6) 3/23/1998              Luis     385.5
 7) 3/23/1998              Andrea   69.85
 8) 4/23/1998              Andrea   51.85
 9) 4/23/1998              Luis     245.39

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
December 7, 2012 at 13:25:57

I need Calculate the Amount of Luis in March

The formula I posted will caclulate Luis for March,
as well as Luis for April & September.
It will also cacluate Andrea for March, April & September.

=SUMPRODUCT(--(TEXT($A$2:$A$9,"mmmm")=$A13),--($B$2:$B$9=B$12), $C$2:$C$9)

but when I put together

You DO NOT need to do that.
You DO NOT need to use a =SUMIFS() function with the formula I posted.
The formula I posted is all you need.

Did you try my formula on the example data you posted?

Take the data and copy/paste into a new clean worksheet.
Then copy the formula I posted and paste it into
Cell B13 (Where you see the XXX)

It should give you the amount your looking for.

  
       A         B         C
 1)   Date      Name     Amount
 2) 9/1/1994    Luis     266.03
 3) 9/1/1994    Luis     233.39
 4) 3/23/1998   Luis     75.85
 5) 3/23/1998   Andrea   410.67
 6) 3/23/1998   Luis     385.5
 7) 3/23/1998   Andrea   69.85
 8) 4/23/1998   Andrea   51.85
 9) 4/23/1998   Luis     245.39
10)                 
11)                 
12) Month       Luis     Andrea
13) March       XXX
14) April       
15) September   

If you want, I can try and explain what is going on.

But, first get the formula to work.

MIKE

http://www.skeptic.com/


Report •


Ask Question