Help with "SUMIF" function

HektikSwift

New Member
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I've made myself a budgeting spreadsheet to keep track of my finances. Now I'm in the process of putting some functions/ formulas to make it easier to use but am having difficulty with SUMIF function.
My budget spreadsheet consists of a calendar type budgeting system where each month has it's on sheet within a workbook and for each day of the month i have made sections to put whether i have made an expense on that day or income.
Basically I'm trying to make it add up all my income (positive numbers) and expenses (negative numbers), separately, for each month at the bottom.
For my total income for the month i have written the formula with the criteria to by >0 and for my total expenses <0, but because the range is to big it doesn't register properly.

Through a lot of trial and error, the only way i managed to make the function work was by having it within a SUM function and had to put in each set of range separately as having all the range in the SUMIF function wouldn't work.
VBA Code:
=SUM(SUMIF(F4:F6,"<0"),SUMIF(H4:H6,"<0"),SUMIF(J4:J6,"<0"),SUMIF(L4:L6,"<0"),SUMIF(N4:N6,"<0"),SUMIF(B10:B12,"<0"),SUMIF(D10:D12,"<0"),SUMIF(F10:F12,"<0"),SUMIF(H10:H12,"<0"),SUMIF(J10:J12,"<0"),SUMIF(L10:L12,"<0"),SUMIF(N10:N12,"<0"),SUMIF(B16:B18,"<0"),SUMIF(D16:D18,"<0"),SUMIF(F16:F18,"<0"),SUMIF(H16:H18,"<0"),SUMIF(J16:J18,"<0"),SUMIF(L16:L18,"<0"),SUMIF(N16:N18,"<0"),SUMIF(B22:B24,"<0"),SUMIF(D22:D24,"<0"),SUMIF(F22:F24,"<0"),SUMIF(H22:H24,"<0"),SUMIF(J22:J24,"<0"),SUMIF(L22:L24,"<0"),SUMIF(N22:N24,"<0"),SUMIF(B28:B30,"<0"),SUMIF(D28:D30,"<0"),SUMIF(F28:F30,"<0"),SUMIF(H28:H30,"<0"),SUMIF(J28:J30,"<0"))

*Note. Each set of range from the formula above (e.g. F4:F6, H4:H6, etc) represents a day of the month*

As you can see this is very long and i would have to repeat this several times (twice for each month) and i can't just copy and paste it as the range isn't the same for each month.
I also tried to put the set of ranges as a named ranged and put that in the formula but that doesn't work either.
Is there a simpler way i can manage this?

Thanks
 
Thanks - you couldn't have expected us to guess that layout, merged cells, mix of text/numbers to be summed/numbers to be ignored! ;)

Do you have the FILTER function in your Excel 365?

HektikSwift 2020-07-25 1.xlsm
ABCDEFGHIJKLMN
1MONTUEWEDTHUFRISATSUN
229301PAY DAY2345
3
4SAL200000PB-1000
500000
600000
770007000700069006900
8678PAY DAY9101112
9
10WS-2000SAL30000AM-11.9900
11CF-50000000
120000000
1366506650965096509638.019638.019638.01
14131415PAY DAY16171819
15
16WS-200NF-19.99SAL1500000IR420.69
17CF-50000000
180000000
199388.019368.0210868.0210868.0210868.0210868.0211288.71
20202122PAY DAY23242526
21
22WS-2000SAL320000LR-6969.690
23CF-55000000
240000000
2511033.7111033.7114233.7114233.7114233.717264.027264.02
26272829PAY DAY303112
27
28WS-2000SAL300000
29CF-500000
3000000
317014.027014.0210014.0210014.0210014.02
322/01/19003/01/1900Notes
33MONTH INCOME:MONTH EXPENSES:MONTH PROFIT:
3413120.69-8106.675014.02
Sum
Cell Formulas
RangeFormula
F34F34=SUM(IF(FILTER(A2:N31,A1:N1="")>0,FILTER(A2:N31,A1:N1="")))
H34H34=SUM(IF(FILTER(A2:N31,A1:N1="")<0,FILTER(A2:N31,A1:N1="")))
J34J34=SUM(F39+H39)

Some months utilise to 2 squares next to the notes section as well, will this formula work for those months as well if i just update the range? or will this turn into a disjoint range?
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You would need to move the summary down to below those rows and increase the ranges in the formula to include those extra rows right across all the same columns. Or move the summary over the right of the calendar somewhere.
 
Upvote 0
You would need to move the summary down to below those rows and increase the ranges in the formula to include those extra rows right across all the same columns. Or move the summary over the right of the calendar somewhere.
Yeah, i figured this would be the best. Thanks mate
 
Upvote 0
I made a Budget plan some years ago, covering each Month, and made with separated income/expenses in each Month, and totals for these, and summary for the whole Year. It has build in, more options for configuration, as well as showing Holidays through the Year. It's originally Danish, but I have changed it to an English version (US Holidays). If you can use it, it's free!
 
Upvote 0
Can you guys please explain what the formulas you provided actually does? e.g. ... IFERROR
SUM((IFERROR((A2:N30)*(A2:N30>0),0))*(A1:N1=""))

First multiply the cells in the range to recognize which ones are> 0, but in the cells where you have text it throws an error, what IFERROR does is to return a 0 in the results with #error.

Some months utilise to 2 squares next to the notes section as well, will this formula work for those months as well if i just update the range? or will this turn into a disjoint range?
My answer in post #2 uses a continuous range, right there is the example to use a named range.
On the other hand the second formula in post #6 would not work if you put values or texts in all cells of range A1 to N1.
 
Upvote 0
SUM((IFERROR((A2:N30)*(A2:N30>0),0))*(A1:N1=""))

First multiply the cells in the range to recognize which ones are> 0, but in the cells where you have text it throws an error, what IFERROR does is to return a 0 in the results with #error.


My answer in post #2 uses a continuous range, right there is the example to use a named range.
On the other hand the second formula in post #6 would not work if you put values or texts in all cells of range A1 to N1.

I see, thanks mate
 
Upvote 0
I made a Budget plan some years ago, covering each Month, and made with separated income/expenses in each Month, and totals for these, and summary for the whole Year. It has build in, more options for configuration, as well as showing Holidays through the Year. It's originally Danish, but I have changed it to an English version (US Holidays). If you can use it, it's free!

Not working for me
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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