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
 

HektikSwift

New Member
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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?
 

Some videos you may like

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
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,635
Members
410,861
Latest member
Victor96
Top