# 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.ThanksThe 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

#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() functionwould 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 rowsDrag Across one columnThis should get you your totals.See how that works for you.MIKEhttp://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 GladwellMIKEhttp://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 SUMIFSI need Calculate the Amount of Luis in MarchThanks again for the help, and my apologies for misunderstand your postJose

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 MarchThe 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 togetherYou 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 intoCell 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.MIKEhttp://www.skeptic.com/

Report •