monthly totals

[nategast]

This one might be easy for you guys, but i've been having trouble, i'm fairly new to excel. My data looks like this: fairly random entries

date ______ name ___ cost
5/7/2010 __ girl1 ____ $510.00
5/12/2010 _ guy2 ____ $1,469.00
5/3/2010 __ guy1 ____ $433.50
3/29/2010 _ guy1 ____ $433.50
5/7/2010 __ guy2 ____ $229.50
4/6/2010 __ guy3 ____ $1,490.00
5/7/2010 __ girl2 ____ $127.50
5/7/2010 __ girl2 ____ $1,606.50


and i want, somewhere else in the document, to be able to post totals for different conditions from the list. like these:

monthly total --or-- name total
mar $ - - - - - - - - -guy1 $
apr $ - - - - - - - - - girl2 $
may $

I have used formulas with VLOOKUP and DSUM, among others, but I'm having trouble getting the coding right. Any help or a link to help would be great. Thanks

 

[btadams]

Try the Sumproduct function, for example using what you posted,

=SUMPRODUCT((MONTH($A$2:$A$17)=5)*($B$2:$B$17="guy2")*($C$2:$C$17))

would return the total for guy2 in April (month = 5)

 

[doublej_jj]

You are going to love sumproduct. With your dates in column A, names in column B, and Cost in column C, assume your data runs from row 2 to row 20.

Cell E2 a user puts a date.
Cell E3 a user puts a name.

Cell E4 = Sumproduct((A2:A20=C2)*(B2:B20=C3)*(C2:C20))

This is good for one specfic date. A little more flexibility

Cell E2 a user puts a start date
Cell E3 a user puts an end date
Cell E4 a user puts a name

Cell E5 = Sumproduct((A2:A20>=E2)*(A2:A20<=E3)*(B2:B20=C3)*(C2:C20)

Study the sumproduct formula, you'll love it. Hope this works.

Jeff

 

[nategast]

Thanks a lot guys i have not tried that one yet.