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

 

[jeffreybrown]

Hi Paul,

Here is an option...

Place this in J9 and copy down to J20
=SUMPRODUCT(--(MONTH($B$9:$B$910)=ROW(A1)),$D$9:$D$910)

If you want to use the Sumif construct you would have to enter it as an array:

=SUM(IF(MONTH($B$9:$B$910)=ROW(A1),$D$9:$D$910,0))

IMPORTANT
This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

 

[PaulS]

I had to try a number of times, but finally I got the array formula to work.
Thank you.
P

 

[PaulS]

One more question:
(I moved data to other columns)
The row number of the last transaction is always in cell AL10.
How do I change the formula
=SUM(IF(MONTH($B$9:$B$xxx)=ROW(A1);M$9:M$xxx;0))
xxx must have the value in cell AL10
Thanks so far.

 

[jeffreybrown]

Paul,

Could you post a small mock-up of sample date with with you have and expected results?

Excel Jeanie

 

[PaulS]

Jeffrey, it will probably cost a lot of time to find out how to post a worksheet. In addition to that I have to explain the Dutch words is the worksheet. I will tray to explain what is done.

I have a simple list of cash and bank transactions transformed into a book of expense and income.
Col B: date in yy-mm-dd (dates are not always in chronological order)
Col C: transaction decription (not needed)
Col D to H: amount (5 bankaccounts)
Col J: Via validationlist a type of income/expense is selected
Via a macro the amount for this transaction is placed in the proper column (Columns M -AI).
The number of transactions can vary per year. (Last year we had 900 – this year we will probably end with about 1000)
The first transaction is in row 12. At start of the year the last row is 37. Row 38 (hidden) has some controlinformation – including the row number , which is then also in cell AL10).
When new transactions are added a macro creates empty rows for the user including all the formulas, copied from line 38. These rows are inserted before line 37.
This means that the rows with the control information as well as the row number of the last transaction increase each time new rows are added.
Starting at row 70 (=at the beginning of the year) some analysis data is calculated. Like cost and income per month.
For instance on the row January the amount cost is sum(M70:X70); income is sum(AD70:AH70).
Cells M70 to AH70 have the formula: (here for row Jnauary and col M)
=SUM(IF(MONTH($B$12:$B$xxx)=ROW(A1);M$12:M$xxx;0)) (acording your advise above – it works but for a fixed number of rows!)
$xxx is the last row to count.
As said before cell AL10 always has the row number of the last transaction. This means in the SUM array formula xxx must be replaced by the contensts of cell AL10. This has to be copied into all columns (M to AH) and rows January to December. The sum is calculated for all cells from row 12 to last (in AL10). I assume that the formula stays valid when new rows are being added.
Hope this explains my question.
Thanks for your help
Paul

 

[PaulS]

Jeffrey, ignore my last story. I have almost finished the workbook.
One more question is left:
In the array formula:
=SUM(IF(MONTH($B$12:$B$100)=ROW($A1);M$12:M$100;0))
the last row to add is not always 100.
I want to make that variable depending on the number of transactions.
The last row to count is a value in cell AL10.
How do I replace the $100 in the formula by AL10?
Thanks for your help.
Paul

 

[PaulS]

I have tried to use the INDEX function, but excel does not accept it:

Replaced $B$100 by INDEX(B:B,AL10)
=SUM(IF(MONTH(B$12:INDEX(B:B,AL10))=ROW($A1);M$12:INDEX(M:M,AL10);0))
Any other suggestion?

 

[Colin Legg]

Why not use a pivot table and group the dates by month?

 

[PaulS]

Hi Colin, my knowledge of excel is medium. The subject I know almost nothing about is just the Pivot Table..........! A friend of mine has offered to show me something about this subject, but that does not solve my problem now. If there is a solution via the array formula then I prefer that.
Thanks
Paul