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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

Assuming your data for one month, from cell B4 to N36.
Try this formulas:
varios 24jul2020.xlsm
ABCDEFGHIJKLMNO
1income3271
2expenses-257
3
4-6789101112131415161718
578-910111213141516171819
6891011-121314151617181920
79101112131415161718192021
810111213141516171819202122
911121314151617181920212223
10121314151617-18192021222324
111314151617181920-2122232425
1214151617181920212223-242526
1315161718192021222324252627
1416171819202122232425262728
1517181920212223242526272829
16181920212223242526272829-30
1719202122232425262728-293031
1820212223242526272829303132
1921222324252627282930313233
2022232425262728293031323334
2123242526272829303132333435
222425262728293031-3233343536
2325262728293031323334353637
2426272829303132333435363738
2527282930313233343536373839
2628293031323334353637383940
2729303132333435363738394041
28303132333435-36373839404142
2931323334353637383940414243
3032333435363738394041424344
3133343536373839404142434445
3234353637383940414243444546
3335363738394041424344454647
3436373839-404142434445464748
3537383940414243444546474849
3638394041424344454647484950
37
Hoja3
Cell Formulas
RangeFormula
B1B1=SUMPRODUCT((B4:N36)*(B4:N36>0)*(MOD(COLUMN(B4:N36),2)=0)*(MOD(INT((ROW(B4:N36)/3)-0.1),2)=1))
B2B2=SUMPRODUCT((B4:N36)*(B4:N36<0)*(MOD(COLUMN(B4:N36),2)=0)*(MOD(INT((ROW(B4:N36)/3)-0.1),2)=1))


You can put the cell range of B4:N36 into a named range, for example for January: "Jan", then you can simplify like this:
=SUMPRODUCT((Jan)*(Jan>0)*(MOD(COLUMN(Jan),2)=0)*(MOD(INT((ROW(Jan)/3)-0.1),2)=1))
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

If Dante's formulas do what you want - great!
If not, it would be very helpful to see exactly what one of your monthly sheets looks like with some sample data in it with XL2BB

Knowing the sort of data, layout headings etc might help get the best result.
 

HektikSwift

New Member
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Thanks Dante and Peter for a swift response,
MrExcel seems way better than I had imagined! Great service.

Dante, I don't think I will be able to range all my daily income/expenses as the total balance for each day might play up with the calculations.

I have added a months budget with made up figures and expenses. Hope it helps.
As you can see in the notes section where the month income and month expense are, the current formula I am using is too long and I feel like it could be simplified.

Thanks

Book4
ABCDEFGHIJKLMNOPQ
1MONTUEWEDTHUFRISATSUN
229301PAY DAY2345
3WSWeekly Spending
4SAL$2,000.00$0.00$0.00PB-$100.00$0.00PBPhone Bill
5$0.00$0.00$0.00$0.00$0.00NFNetflix
6$0.00$0.00$0.00$0.00$0.00AMApple Music
7$7,000.00$7,000.00$7,000.00$6,900.00$6,900.00CFCar fuel
8678PAY DAY9101112IRInvestment Return
9SALSalary
10WS-$200.00$0.00SAL$3,000.00$0.00AM-$11.99$0.00$0.00LRLoan Repayments
11CF-$50.00$0.00$0.00$0.00$0.00$0.00$0.00
12$0.00$0.00$0.00$0.00$0.00$0.00$0.00
13$6,650.00$6,650.00$9,650.00$9,650.00$9,638.01$9,638.01$9,638.01
14131415PAY DAY16171819
15
16WS-$200.00NF-$19.99SAL$1,500.00$0.00$0.00$0.00IR$420.69
17CF-$50.00$0.00$0.00$0.00$0.00$0.00$0.00
18$0.00$0.00$0.00$0.00$0.00$0.00$0.00
19$9,388.01$9,368.02$10,868.02$10,868.02$10,868.02$10,868.02$11,288.71
20202122PAY DAY23242526
21
22WS-$200.00$0.00SAL$3,200.00$0.00$0.00LR-$6,969.69$0.00
23CF-$55.00$0.00$0.00$0.00$0.00$0.00$0.00
24$0.00$0.00$0.00$0.00$0.00$0.00$0.00
25$11,033.71$11,033.71$14,233.71$14,233.71$14,233.71$7,264.02$7,264.02
26272829PAY DAY303112
27
28WS-$200.00$0.00SAL$3,000.00$0.00$0.00
29CF-$50.00$0.00$0.00$0.00$0.00
30$0.00$0.00$0.00$0.00$0.00
31$7,014.02$7,014.02$10,014.02$10,014.02$10,014.02
3234Notes
33MONTH INCOME:MONTH EXPENSES:MONTH PROFIT:
34$13,120.69-$8,106.67$5,014.02
35
36
37
JUL 2020
Cell Formulas
RangeFormula
E7E7=5000+F4+F5+F6
G7,I7,K7,M7,C31,E31,G31,I31,C25,E25,G25,I25,K25,M25,C19,E19,G19,I19,K19,M19,C13,E13,G13,I13,K13,M13G7=E7+H4+H5+H6
A13,A31,A25,A19A13=M7+B10+B11+B12
F34F34=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"))
H34H34=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"))
J34J34=SUM(F34+H34)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B18Cell Value<0textNO
B18Cell Value>0textNO
F11:F12Cell Value<0textNO
F11:F12Cell Value>0textNO
F23Cell Value<0textNO
F23Cell Value>0textNO
F22Cell Value<0textNO
F22Cell Value>0textNO
F10Cell Value<0textNO
F10Cell Value>0textNO
F24Cell Value<0textNO
F24Cell Value>0textNO
D22,H22,J22,L22,N22Cell Value<0textNO
D22,H22,J22,L22,N22Cell Value>0textNO
D17Cell Value<0textNO
D17Cell Value>0textNO
D16Cell Value<0textNO
D16Cell Value>0textNO
F16,H16,J16,L16,N16Cell Value<0textNO
F16,H16,J16,L16,N16Cell Value>0textNO
D11Cell Value<0textNO
D11Cell Value>0textNO
J11Cell Value<0textNO
J11Cell Value>0textNO
D10,H10,J10,L10,N10Cell Value<0textNO
D10,H10,J10,L10,N10Cell Value>0textNO
H5:H6Cell Value<0textNO
H5:H6Cell Value>0textNO
F4,H4,J4,L4,N4Cell Value<0textNO
F4,H4,J4,L4,N4Cell Value>0textNO
F34Cell Value<0textNO
F34Cell Value>0textNO
D28,F28,H28,J28Cell Value<0textNO
D28,F28,H28,J28Cell Value>0textNO
J34Cell Value<0textNO
J34Cell Value>0textNO
H34Cell Value<0textNO
H34Cell Value>0textNO
J5:J6,L5:L6,N5:N6,B10:B12,D12,H11:H12,J12,L11:L12,N11:N12,B16:B17,D18,F17:F18,H17:H18,J17:J18,L17:L18,N17:N18,B22:B24,D23:D24,H23:H24,J23:J24,L23:L24,N23:N24,B28:B30,D29:D30,F29:F30,H29:H30,J29:J30Cell Value<0textNO
J5:J6,L5:L6,N5:N6,B10:B12,D12,H11:H12,J12,L11:L12,N11:N12,B16:B17,D18,F17:F18,H17:H18,J17:J18,L17:L18,N17:N18,B22:B24,D23:D24,H23:H24,J23:J24,L23:L24,N23:N24,B28:B30,D29:D30,F29:F30,H29:H30,J29:J30Cell Value>0textNO
F5:F6Cell Value<0textNO
F5:F6Cell Value>0textNO
M26Expression=MONTH(M26)<>MONTH($A$1)textNO
M26Expression=OR(WEEKDAY(M26,1)=1,WEEKDAY(M26,1)=7)textNO
K26Expression=MONTH(K26)<>MONTH($A$1)textNO
K26Expression=OR(WEEKDAY(K26,1)=1,WEEKDAY(K26,1)=7)textNO
I26Expression=MONTH(I26)<>MONTH($A$1)textNO
I26Expression=OR(WEEKDAY(I26,1)=1,WEEKDAY(I26,1)=7)textNO
A26,C26,E26,G26Expression=MONTH(A26)<>MONTH($A$1)textNO
A26,C26,E26,G26Expression=OR(WEEKDAY(A26,1)=1,WEEKDAY(A26,1)=7)textNO
M20Expression=MONTH(M20)<>MONTH($A$1)textNO
M20Expression=OR(WEEKDAY(M20,1)=1,WEEKDAY(M20,1)=7)textNO
K20Expression=MONTH(K20)<>MONTH($A$1)textNO
K20Expression=OR(WEEKDAY(K20,1)=1,WEEKDAY(K20,1)=7)textNO
I20Expression=MONTH(I20)<>MONTH($A$1)textNO
I20Expression=OR(WEEKDAY(I20,1)=1,WEEKDAY(I20,1)=7)textNO
A20,C20,E20,G20Expression=MONTH(A20)<>MONTH($A$1)textNO
A20,C20,E20,G20Expression=OR(WEEKDAY(A20,1)=1,WEEKDAY(A20,1)=7)textNO
M14Expression=MONTH(M14)<>MONTH($A$1)textNO
M14Expression=OR(WEEKDAY(M14,1)=1,WEEKDAY(M14,1)=7)textNO
K14Expression=MONTH(K14)<>MONTH($A$1)textNO
K14Expression=OR(WEEKDAY(K14,1)=1,WEEKDAY(K14,1)=7)textNO
I14Expression=MONTH(I14)<>MONTH($A$1)textNO
I14Expression=OR(WEEKDAY(I14,1)=1,WEEKDAY(I14,1)=7)textNO
A14,C14,E14,G14Expression=MONTH(A14)<>MONTH($A$1)textNO
A14,C14,E14,G14Expression=OR(WEEKDAY(A14,1)=1,WEEKDAY(A14,1)=7)textNO
M8Expression=MONTH(M8)<>MONTH($A$1)textNO
M8Expression=OR(WEEKDAY(M8,1)=1,WEEKDAY(M8,1)=7)textNO
K8Expression=MONTH(K8)<>MONTH($A$1)textNO
K8Expression=OR(WEEKDAY(K8,1)=1,WEEKDAY(K8,1)=7)textNO
I8Expression=MONTH(I8)<>MONTH($A$1)textNO
I8Expression=OR(WEEKDAY(I8,1)=1,WEEKDAY(I8,1)=7)textNO
A8,C8,E8,G8Expression=MONTH(A8)<>MONTH($A$1)textNO
A8,C8,E8,G8Expression=OR(WEEKDAY(A8,1)=1,WEEKDAY(A8,1)=7)textNO
M2Expression=MONTH(M2)<>MONTH($A$1)textNO
M2Expression=OR(WEEKDAY(M2,1)=1,WEEKDAY(M2,1)=7)textNO
K2Expression=MONTH(K2)<>MONTH($A$1)textNO
K2Expression=OR(WEEKDAY(K2,1)=1,WEEKDAY(K2,1)=7)textNO
I2Expression=MONTH(I2)<>MONTH($A$1)textNO
I2Expression=OR(WEEKDAY(I2,1)=1,WEEKDAY(I2,1)=7)textNO
A32,C32Expression=MONTH(A32)<>MONTH($A$1)textNO
A2,C2,E2,G2Expression=MONTH(A2)<>MONTH($A$1)textNO
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Dante, I don't think I will be able to range all my daily income/expenses as the total balance for each day might play up with the calculations.

Here is the updated formula with the new information that you provided.

varios 24jul2020.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
3202/01/190003/01/1900Notes
33MONTH INCOME:MONTH EXPENSES:MONTH PROFIT:
3413120.69-8106.675014.02
Hoja5
Cell Formulas
RangeFormula
F34F34=SUMPRODUCT((IFERROR((B4:N30)*(B4:N30>0),0))*(MOD(COLUMN(B4:N30),2)=0)*(MOD(INT((ROW(B4:N30)/3)-0.1),2)=1))
H34H34=SUMPRODUCT((IFERROR((B4:N30)*(B4:N30<0),0))*(MOD(COLUMN(B4:N30),2)=0)*(MOD(INT((ROW(B4:N30)/3)-0.1),2)=1))
J34J34=SUM(F34+H34)
Press CTRL+SHIFT+ENTER to enter array formulas.


___________________________________________________________________________________________________________

If you don't have the Filter function, and taking Peter's idea, you could use the following option:
varios 24jul2020.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
3202/01/190003/01/1900Notes
33MONTH INCOME:MONTH EXPENSES:MONTH PROFIT:
3413120.69-8106.675014.02
Hoja5
Cell Formulas
RangeFormula
F34F34=SUM((IFERROR((A2:N30)*(A2:N30>0),0))*(A1:N1=""))
H34H34=SUM((IFERROR((A2:N30)*(A2:N30<0),0))*(A1:N1=""))
J34J34=SUM(F34+H34)
Press CTRL+SHIFT+ENTER to enter array formulas.


________________________________________________________________________________________________________________________________________________________

By the way, your sheet only contemplates 5 weeks per month, but there are months that cover 6 weeks, for example May-2020 or Oct-2020.

___
 

HektikSwift

New Member
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web

ADVERTISEMENT

Thanks once again Peter and Dante,

Peter - Haha, I didn't know about XL2BB. And yes the FILTER function works on my Excel.
Dante - Each sheet has 37 boxed squares representing days set out which is plenty for any month, even months with 6 weeks will fit.

Both formulas are heaps better than what I came up with but I can't seem to understand how it works. I'm kinda new to Excel and am not familiar with most functions and what they do. I generally like to understand every part of a formula before implementing it. Can you guys please explain what the formulas you provided actually does? e.g. FILTER, IFERROR and what data the formula needs/takes from table to do the calculations.

Also is it possible to use Named Ranges with each formula you guys provided?
I was thinking of having a named range made from all the cells that I input how much money was made/spent per day (3 cells per day of the month) for each month and having that named range put into the formula.

Thanks
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
Can you guys please explain what the formulas you provided actually does?
=SUM(IF(FILTER(A2:N31,A1:N1="")>0,FILTER(A2:N31,A1:N1="")))

FILTER(A2:N31,A1:N1="")
This takes the whole range A2:N31 and returns all the cells where row 1 in that column is blank. Because you have merged cells and the day names are actually in columns A,C, E etc those columns are not included in the returned cells. That eliminates all the 'day numbers' since they are in those same columns A, C, ... It also eliminates the numerical values in the blue rows since they are the result of formulas also in columns A. C, ...

What is left is your income and expense values, blank cells and some text (eg PAY DAY)

IF(FILTER(A2:N31,A1:N1="")>0,FILTER(A2:N31,A1:N1=""))
If the cells described above are > 0 then return those values. This eliminates the negatives and keeps positive values and text.
SUM ignores text so it returns the required sum of the positive values.


Also is it possible to use Named Ranges with each formula you guys provided?
I don't think that a named range made up of disjoint areas will work with my suggestion. Even if it did I think that would make the formula more complicated.
 

HektikSwift

New Member
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
=SUM(IF(FILTER(A2:N31,A1:N1="")>0,FILTER(A2:N31,A1:N1="")))

FILTER(A2:N31,A1:N1="")
This takes the whole range A2:N31 and returns all the cells where row 1 in that column is blank. Because you have merged cells and the day names are actually in columns A,C, E etc those columns are not included in the returned cells. That eliminates all the 'day numbers' since they are in those same columns A, C, ... It also eliminates the numerical values in the blue rows since they are the result of formulas also in columns A. C, ...

What is left is your income and expense values, blank cells and some text (eg PAY DAY)

IF(FILTER(A2:N31,A1:N1="")>0,FILTER(A2:N31,A1:N1=""))
If the cells described above are > 0 then return those values. This eliminates the negatives and keeps positive values and text.
SUM ignores text so it returns the required sum of the positive values.


I don't think that a named range made up of disjoint areas will work with my suggestion. Even if it did I think that would make the formula more complicated.

I see, this makes perfect sense to me now. Thanks heaps!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,536
Messages
5,548,626
Members
410,859
Latest member
RamIndia
Top