# Help with "SUMIF" function

#### HektikSwift

##### New Member
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

#### HektikSwift

##### New Member
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?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Peter_SSs

##### MrExcel MVP, Moderator
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.

#### HektikSwift

##### New Member
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

#### ebea

##### Board Regular

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!

#### DanteAmor

##### Well-known Member
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.

#### HektikSwift

##### New Member
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

#### HektikSwift

##### New Member
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

Replies
3
Views
37
Replies
3
Views
260
Replies
6
Views
42
Replies
5
Views
56
Replies
3
Views
20