SUMIF problem

[PaulS]

I tried to get totals per months of a list of transactions:
Col B: date in yy-mm-dd (dates are not always in chronological order)
Col C: transaction decription (not needed)
Col D: amount
Cells I9 to I20 month in numbers 1 - 12
I would like to have the totals per month in cells J9 to J20
I have tried
in J9 (total for January):
=SUMIF(B$9:B$910;MONTH(B$9:B$910)=I9;D$9:D$910)
in J10 (February):
=SUMIF(B$9:B$910;MONTH(B$9:B$910)=I10;D$9:D$910)
The formula returns no error, however the result is always 0 (zero).
What is wrong in this formula?
Thanks for your help,
Paul

 

[Colin Legg]

Hi Paul,

Pivot table approach is super easy. Step by step instructions for XL 2003:

• Insert > Name > Define
• In the Name field, type in PvtRange
• In the Refers to: field, type in this formula and click add. (Change Sheet1 for the name of your sheet and if the sheet name has spaces then suround it with single quotes, eg 'My Sheet'!$D:$D).

=Sheet1!$B$1:index(Sheet1!$D:$D,Sheet1!$AL$9)

• Select the cell where you want the table to appear.
• Data > PivotTable
• Next >
• In the Range: field, type in pvtRange
• Finish

• Left click on the Date field in the pivot field window and drag it onto the Row Fields section of the pivot table.
• Right click on the Date row field > Group and Show Detail > Group > hold down the CTRL key and choose Months and Years. Click OK. [If your dates do not overlap years or if you want the same month to be totalled across different years then you can only choose Months here].
• Left click on the Column field in the pivot field window and drag it onto the Data Items section of the pivot table.

You are done and your date should look like this:

Excel Workbook
	B	C	D	E	I	J	K	AL
1	Date	Some column	Amount
2	10-10-08		43
3	10-11-05		32
4	10-12-17		83
5	10-05-21		18
6	10-03-12		51
7	10-06-04		73
8	10-09-24		85					Last Row
9	10-06-04		43		Sum of Amount			28
10	10-09-10		70		Years	Date	Total
11	10-05-21		40		2010	Jan	147
12	10-07-16		36			Feb	45
13	10-09-10		7			Mar	51
14	10-09-24		40			Apr	50
15	10-08-27		58			May	67
16	10-09-24		12			Jun	189
17	10-05-21		9			Jul	36
18	10-01-15		61			Aug	209
19	10-02-12		26			Sep	214
20	10-08-27		34			Oct	128
21	10-06-18		73			Nov	32
22	10-04-23		50			Dec	138
23	10-01-29		86		Grand Total		1306
24	10-08-27		92
25	10-12-17		55
26	10-02-12		19
27	10-10-22		85
28	10-08-27		25
Sheet1

Excel 2003

 

[Colin Legg]

In case you are interested, I think that the formula approach you were trying would look like this:

Excel Workbook
	B	D	E	I	J
1	Date	Amount
2	10-10-08	43
3	10-11-05	32
4	10-12-17	83
5	10-05-21	18
6	10-03-12	51
7	10-06-04	73
8	10-09-24	85		Month	Total
9	10-06-04	43		1	147
10	10-09-10	70		2	45
11	10-05-21	40		3	51
12	10-07-16	36		4	50
13	10-09-10	7		5	67
14	10-09-24	40		6	189
15	10-08-27	58		7	36
16	10-09-24	12		8	209
17	10-05-21	9		9	214
18	10-01-15	61		10	128
19	10-02-12	26		11	32
20	10-08-27	34		12	138
21	10-06-18	73
22	10-04-23	50
23	10-01-29	86
24	10-08-27	92
25	10-12-17	55
26	10-02-12	19
27	10-10-22	85
28	10-08-27	25
Sheet3

Excel 2003

Cell Formulas
Range	Formula
J9	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I9),$D$2:INDEX($D:$D,$AL$9))
J10	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I10),$D$2:INDEX($D:$D,$AL$9))
J11	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I11),$D$2:INDEX($D:$D,$AL$9))
J12	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I12),$D$2:INDEX($D:$D,$AL$9))
J13	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I13),$D$2:INDEX($D:$D,$AL$9))
J14	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I14),$D$2:INDEX($D:$D,$AL$9))
J15	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I15),$D$2:INDEX($D:$D,$AL$9))
J16	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I16),$D$2:INDEX($D:$D,$AL$9))
J17	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I17),$D$2:INDEX($D:$D,$AL$9))
J18	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I18),$D$2:INDEX($D:$D,$AL$9))
J19	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I19),$D$2:INDEX($D:$D,$AL$9))
J20	=SUMPRODUCT(--(MONTH($B$2:INDEX($B:$B,$AL$9))=I20),$D$2:INDEX($D:$D,$AL$9))

 

[PaulS]

Thank you for your effort.
I used your SUMPRODUCT formula and I get a formula error at the second $B in the first INDEX function. Then I changed my formula - added the $-signs in the 'SUM(IF....'. Surprise: error on the same spot. Is it possible that the array formula does not work in this way?
I think I have to try your pivot table suggestion.
In my worksheet more than columns to be totaled.
It is like this - see your pivot table under 'sheet3'
- The monthly totals start in row 30 (row 28 is last - '28' is in cell AL10)
- Col E has a productcode - that decides in which column the amount of col D is also placed in one of col M to V.
- a pivot table as described above must give totals per month in each col starting in row 30.
So instead of using the amounts in col D, I can use the amounts in columns M to V.
The totals of the pivot tables must equal the totals in the worksheet.
This will give an overview of the costs per product per month.
Now I have to start my study of 'Pivot tables'!
Paul

 

[PaulS]

Colin, is it possible that there is a "problem" in the INDEX function in my version of Excel?
After entering the formula in the Name field as described by you I get an error on the second argument of the INDEX function. This happens on the same spot as I got in the 'SUM(IF...' and 'SUMPRODUCT(....' formulas. That cannot be a coincidence!