I have a table where of info with the following headings

Date Vendor Reason

I want to SUM all Vendors with a particular Reason (only 3 different
reasons) by month so I could have another table like

Month Reason 1 Reason 2 Reason 3
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

I have tried experimenting with sumproduct and countif but can seem to get
the syntax right.

Could anyone help?

TIA

Hi

i would use a pivot table for this -
click inside your data area
choose data / pivot table and pivot chart report
choose next
the range should come up automatically - check that it is right and click
next
click on the layout button and drag the dates to the rows,
drag the reasons to the columns
drag the vendors to the data area
click OK click FINISH
now
right mouse click on the Date heading choose Group and Show Detail
choose Group
Months should be selected already so click the OK button

Hope this helps
Cheers
JulieD


"Edgar Thoemmes" wrote in message
...
I have a table where of info with the following headings

Date Vendor Reason

I want to SUM all Vendors with a particular Reason (only 3 different
reasons) by month so I could have another table like

Month Reason 1 Reason 2 Reason 3
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

I have tried experimenting with sumproduct and countif but can seem to get
the syntax right.

Could anyone help?

TIA

However, should you wish to use formulas then with your data in
A2:C20
and the output table in E3:H15
use the following formula in cell F4
=SUMPRODUCT(--(TEXT($A$2:$A$20, "mmm")=$E4),--($C$2:$C$20=F$3))
and fill down and across

Cheers
JulieD

"JulieD" wrote in message
...
Hi

i would use a pivot table for this -
click inside your data area
choose data / pivot table and pivot chart report
choose next
the range should come up automatically - check that it is right and click
next
click on the layout button and drag the dates to the rows,
drag the reasons to the columns
drag the vendors to the data area
click OK click FINISH
now
right mouse click on the Date heading choose Group and Show Detail
choose Group
Months should be selected already so click the OK button

Hope this helps
Cheers
JulieD


"Edgar Thoemmes" wrote in
message ...
I have a table where of info with the following headings

Date Vendor Reason

I want to SUM all Vendors with a particular Reason (only 3 different
reasons) by month so I could have another table like

Month Reason 1 Reason 2 Reason 3
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

I have tried experimenting with sumproduct and countif but can seem to
get
the syntax right.

Could anyone help?

TIA

how about
=sumproduct((a2:a200="Jan")*(b2:b200=1))
and if you had a col c you could sum that
=sumproduct((a2:a200="Jan")*(b2:b200=1)*c2:c200)

--
Don Guillett
SalesAid Software

"Edgar Thoemmes" wrote in message
...
I have a table where of info with the following headings

Date Vendor Reason

I want to SUM all Vendors with a particular Reason (only 3 different
reasons) by month so I could have another table like

Month Reason 1 Reason 2 Reason 3
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

I have tried experimenting with sumproduct and countif but can seem to get
the syntax right.

Could anyone help?

TIA

Maybe your table didn't come out correct but I have a hard time seeing what's
supposed to be summed? If you want to sum for instance March for Reason2
meaning it will lookup "Mar" and return what is in the third column you can
use (Assume the whole table is calle MyTable)

=SUMIF(INDEX(MyTable,,1),"Mar",INDEX(MyTable,,3))

Date Reason1 Reason2 Reason3

Jan 1 2 1
Feb 4 4 8
Mar 2 6 1

will return 6 for March



Regards,

Peo Sjoblom

"Edgar Thoemmes" wrote:

I have a table where of info with the following headings

Date Vendor Reason

I want to SUM all Vendors with a particular Reason (only 3 different
reasons) by month so I could have another table like

Month Reason 1 Reason 2 Reason 3
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

I have tried experimenting with sumproduct and countif but can seem to get
the syntax right.

Could anyone help?

TIA