SUMIF problem

PaulS

Board Regular
Joined
Feb 26, 2006
Messages
66
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Last edited:
Upvote 0
I had to try a number of times, but finally I got the array formula to work.
Thank you.
P
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top