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

=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

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

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

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

Suggest you put all data into one worksheet and into columns
eg.
Col A has DUE DATE (as dd/mm/yy)
Col B month
Col C has AMOUNT (as dollars)
Col D has CATEGORIES (Rent, Utilities, Car etc)

for yr reporting, you are either use pivot table or sumproduct formula
(very similar to sumif, except you can put more than multi-conditions).

The above suggestion is also useful if you have to change your reporting
dimension, you can also extra set of info by adding columns.

I hope this help


"Ricky" wrote in message
. au...
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