Sum values by month

[Guest]

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

 

[JulieD]

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

 

[JulieD]

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

 

[Don Guillett]

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)

 

[Guest]

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