Sum If in a Budget
you're welcome. also, i meant to say
If G1 is actually a *number* then replace G$1 with
Text(G$1, "mmm yy")
"Ricky" wrote:
Perfect JBM - thanks!
JMB wrote:
=Sumproduct(--(Text(DueDates, "mmm yy")=G$1), --(Categories=$F2), Amount)
replace Duedates, Categories, and Amount with the proper range references.
Also, I'm assuming G1 is text. If it is actually text then replace G$1 with
Text(G$1, "mmm yy")
"Ricky" wrote:
I'm trying to sum monthly amounts for a given month in my budget
spreadsheet, without success.
Col A has DUE DATE (as dd/mm/yy)
Col B has AMOUNT (as dollars)
Col C has CATEGORIES (Rent, Utilities, Car etc)
Elsewhere I have a table headed with MONTHS, and a list all my CATEGORIES.
I want the table populated with $ amounts being the total of each
CATEGORIES under each of the MONTHS cols. i.e.
In cells G1-R1, I have col headers "Jan 09" through to "Dec 09", and
In cell F2, I enter a category "Car"
In cell G2, I want the total amount spent in "Jan 09" for "Car"
Something like "Sum if DUE DATE is in JAN 09, and CATEGORY=CAR"
Could someone help out with a formular that will achieve this.
I realize I can get this result using filters, but this does not satisfy
my reporting needs.
Tks
|