Help with "SUMIF" function

HektikSwift · Jul 24, 2020

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

DanteAmor · Jul 24, 2020

Hi and welcome to MrExcel.

Assuming your data for one month, from cell B4 to N36.
Try this formulas:

varios 24jul2020.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

income

3271

2

expenses

-257

3

4

-6

7

8

9

10

11

12

13

14

15

16

17

18

5

7

8

-9

10

11

12

13

14

15

16

17

18

19

6

8

9

10

11

-12

13

14

15

16

17

18

19

20

7

9

10

11

12

13

14

15

16

17

18

19

20

21

8

10

11

12

13

14

15

16

17

18

19

20

21

22

9

11

12

13

14

15

16

17

18

19

20

21

22

23

10

12

13

14

15

16

17

-18

19

20

21

22

23

24

11

13

14

15

16

17

18

19

20

-21

22

23

24

25

12

14

15

16

17

18

19

20

21

22

23

-24

25

26

13

15

16

17

18

19

20

21

22

23

24

25

26

27

14

16

17

18

19

20

21

22

23

24

25

26

27

28

15

17

18

19

20

21

22

23

24

25

26

27

28

29

16

18

19

20

21

22

23

24

25

26

27

28

29

-30

17

19

20

21

22

23

24

25

26

27

28

-29

30

31

18

20

21

22

23

24

25

26

27

28

29

30

31

32

19

21

22

23

24

25

26

27

28

29

30

31

32

33

20

22

23

24

25

26

27

28

29

30

31

32

33

34

21

23

24

25

26

27

28

29

30

31

32

33

34

35

22

24

25

26

27

28

29

30

31

-32

33

34

35

36

23

25

26

27

28

29

30

31

32

33

34

35

36

37

24

26

27

28

29

30

31

32

33

34

35

36

37

38

25

27

28

29

30

31

32

33

34

35

36

37

38

39

26

28

29

30

31

32

33

34

35

36

37

38

39

40

27

29

30

31

32

33

34

35

36

37

38

39

40

41

28

30

31

32

33

34

35

-36

37

38

39

40

41

42

29

31

32

33

34

35

36

37

38

39

40

41

42

43

30

32

33

34

35

36

37

38

39

40

41

42

43

44

31

33

34

35

36

37

38

39

40

41

42

43

44

45

32

34

35

36

37

38

39

40

41

42

43

44

45

46

33

35

36

37

38

39

40

41

42

43

44

45

46

47

34

36

37

38

39

-40

41

42

43

44

45

46

47

48

35

37

38

39

40

41

42

43

44

45

46

47

48

49

36

38

39

40

41

42

43

44

45

46

47

48

49

50

37

Hoja3

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT((B4:N36)(B4:N36>0)(MOD(COLUMN(B4:N36),2)=0)*(MOD(INT((ROW(B4:N36)/3)-0.1),2)=1))
B2	B2	=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))

Peter_SSs · Jul 25, 2020

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 · Jul 25, 2020

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

MON

TUE

WED

THU

FRI

SAT

SUN

2

29

30

1

PAY DAY

2

3

4

5

3

WS

Weekly Spending

4

SAL

$2,000.00

$0.00

PB

-$100.00

$0.00

PB

Phone Bill

5

$0.00

NF

Netflix

6

$0.00

AM

Apple Music

7

$7,000.00

$6,900.00

CF

Car fuel

8

6

7

8

PAY DAY

9

10

11

12

IR

Investment Return

9

SAL

Salary

10

WS

-$200.00

$0.00

SAL

$3,000.00

$0.00

AM

-$11.99

$0.00

LR

Loan Repayments

11

CF

-$50.00

$0.00

12

$0.00

13

$6,650.00

$9,650.00

$9,638.01

14

13

14

15

PAY DAY

16

17

18

19

15

16

WS

-$200.00

NF

-$19.99

SAL

$1,500.00

$0.00

IR

$420.69

17

CF

-$50.00

$0.00

18

$0.00

19

20

21

22

PAY DAY

23

24

25

26

21

22

WS

-$200.00

$0.00

SAL

$3,200.00

$0.00

LR

-$6,969.69

$0.00

23

CF

-$55.00

$0.00

24

$0.00

25

$11,033.71

$14,233.71

$7,264.02

26

27

28

29

PAY DAY

30

31

1

2

27

28

WS

-$200.00

$0.00

SAL

$3,000.00

$0.00

29

CF

-$50.00

$0.00

30

$0.00

31

$7,014.02

$10,014.02

32

3

4

Notes

33

MONTH INCOME:

MONTH EXPENSES:

MONTH PROFIT:

34

$13,120.69

-$8,106.67

$5,014.02

35

36

37

JUL 2020

Cell Formulas
Range	Formula
E7	E7	=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,M13	G7	=E7+H4+H5+H6
A13,A31,A25,A19	A13	=M7+B10+B11+B12
F34	F34	=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"))
H34	H34	=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"))
J34	J34	=SUM(F34+H34)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B18	Cell Value	<0	text	NO
B18	Cell Value	>0	text	NO
F11:F12	Cell Value	<0	text	NO
F11:F12	Cell Value	>0	text	NO
F23	Cell Value	<0	text	NO
F23	Cell Value	>0	text	NO
F22	Cell Value	<0	text	NO
F22	Cell Value	>0	text	NO
F10	Cell Value	<0	text	NO
F10	Cell Value	>0	text	NO
F24	Cell Value	<0	text	NO
F24	Cell Value	>0	text	NO
D22,H22,J22,L22,N22	Cell Value	<0	text	NO
D22,H22,J22,L22,N22	Cell Value	>0	text	NO
D17	Cell Value	<0	text	NO
D17	Cell Value	>0	text	NO
D16	Cell Value	<0	text	NO
D16	Cell Value	>0	text	NO
F16,H16,J16,L16,N16	Cell Value	<0	text	NO
F16,H16,J16,L16,N16	Cell Value	>0	text	NO
D11	Cell Value	<0	text	NO
D11	Cell Value	>0	text	NO
J11	Cell Value	<0	text	NO
J11	Cell Value	>0	text	NO
D10,H10,J10,L10,N10	Cell Value	<0	text	NO
D10,H10,J10,L10,N10	Cell Value	>0	text	NO
H5:H6	Cell Value	<0	text	NO
H5:H6	Cell Value	>0	text	NO
F4,H4,J4,L4,N4	Cell Value	<0	text	NO
F4,H4,J4,L4,N4	Cell Value	>0	text	NO
F34	Cell Value	<0	text	NO
F34	Cell Value	>0	text	NO
D28,F28,H28,J28	Cell Value	<0	text	NO
D28,F28,H28,J28	Cell Value	>0	text	NO
J34	Cell Value	<0	text	NO
J34	Cell Value	>0	text	NO
H34	Cell Value	<0	text	NO
H34	Cell Value	>0	text	NO
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:J30	Cell Value	<0	text	NO
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:J30	Cell Value	>0	text	NO
F5:F6	Cell Value	<0	text	NO
F5:F6	Cell Value	>0	text	NO
M26	Expression	=MONTH(M26)<>MONTH($A$1)	text	NO
M26	Expression	=OR(WEEKDAY(M26,1)=1,WEEKDAY(M26,1)=7)	text	NO
K26	Expression	=MONTH(K26)<>MONTH($A$1)	text	NO
K26	Expression	=OR(WEEKDAY(K26,1)=1,WEEKDAY(K26,1)=7)	text	NO
I26	Expression	=MONTH(I26)<>MONTH($A$1)	text	NO
I26	Expression	=OR(WEEKDAY(I26,1)=1,WEEKDAY(I26,1)=7)	text	NO
A26,C26,E26,G26	Expression	=MONTH(A26)<>MONTH($A$1)	text	NO
A26,C26,E26,G26	Expression	=OR(WEEKDAY(A26,1)=1,WEEKDAY(A26,1)=7)	text	NO
M20	Expression	=MONTH(M20)<>MONTH($A$1)	text	NO
M20	Expression	=OR(WEEKDAY(M20,1)=1,WEEKDAY(M20,1)=7)	text	NO
K20	Expression	=MONTH(K20)<>MONTH($A$1)	text	NO
K20	Expression	=OR(WEEKDAY(K20,1)=1,WEEKDAY(K20,1)=7)	text	NO
I20	Expression	=MONTH(I20)<>MONTH($A$1)	text	NO
I20	Expression	=OR(WEEKDAY(I20,1)=1,WEEKDAY(I20,1)=7)	text	NO
A20,C20,E20,G20	Expression	=MONTH(A20)<>MONTH($A$1)	text	NO
A20,C20,E20,G20	Expression	=OR(WEEKDAY(A20,1)=1,WEEKDAY(A20,1)=7)	text	NO
M14	Expression	=MONTH(M14)<>MONTH($A$1)	text	NO
M14	Expression	=OR(WEEKDAY(M14,1)=1,WEEKDAY(M14,1)=7)	text	NO
K14	Expression	=MONTH(K14)<>MONTH($A$1)	text	NO
K14	Expression	=OR(WEEKDAY(K14,1)=1,WEEKDAY(K14,1)=7)	text	NO
I14	Expression	=MONTH(I14)<>MONTH($A$1)	text	NO
I14	Expression	=OR(WEEKDAY(I14,1)=1,WEEKDAY(I14,1)=7)	text	NO
A14,C14,E14,G14	Expression	=MONTH(A14)<>MONTH($A$1)	text	NO
A14,C14,E14,G14	Expression	=OR(WEEKDAY(A14,1)=1,WEEKDAY(A14,1)=7)	text	NO
M8	Expression	=MONTH(M8)<>MONTH($A$1)	text	NO
M8	Expression	=OR(WEEKDAY(M8,1)=1,WEEKDAY(M8,1)=7)	text	NO
K8	Expression	=MONTH(K8)<>MONTH($A$1)	text	NO
K8	Expression	=OR(WEEKDAY(K8,1)=1,WEEKDAY(K8,1)=7)	text	NO
I8	Expression	=MONTH(I8)<>MONTH($A$1)	text	NO
I8	Expression	=OR(WEEKDAY(I8,1)=1,WEEKDAY(I8,1)=7)	text	NO
A8,C8,E8,G8	Expression	=MONTH(A8)<>MONTH($A$1)	text	NO
A8,C8,E8,G8	Expression	=OR(WEEKDAY(A8,1)=1,WEEKDAY(A8,1)=7)	text	NO
M2	Expression	=MONTH(M2)<>MONTH($A$1)	text	NO
M2	Expression	=OR(WEEKDAY(M2,1)=1,WEEKDAY(M2,1)=7)	text	NO
K2	Expression	=MONTH(K2)<>MONTH($A$1)	text	NO
K2	Expression	=OR(WEEKDAY(K2,1)=1,WEEKDAY(K2,1)=7)	text	NO
I2	Expression	=MONTH(I2)<>MONTH($A$1)	text	NO
I2	Expression	=OR(WEEKDAY(I2,1)=1,WEEKDAY(I2,1)=7)	text	NO
A32,C32	Expression	=MONTH(A32)<>MONTH($A$1)	text	NO
A2,C2,E2,G2	Expression	=MONTH(A2)<>MONTH($A$1)	text	NO

Peter_SSs · Jul 25, 2020

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

MON

TUE

WED

THU

FRI

SAT

SUN

2

29

30

1

PAY DAY

2

3

4

5

3

4

SAL

2000

0

PB

-100

0

5

0

6

0

7

7000

6900

8

6

7

8

PAY DAY

9

10

11

12

9

10

WS

-200

0

SAL

3000

0

AM

-11.99

0

11

CF

-50

0

12

0

13

6650

9650

9638.01

14

13

14

15

PAY DAY

16

17

18

19

15

16

WS

-200

NF

-19.99

SAL

1500

0

IR

420.69

17

CF

-50

0

18

0

19

9388.01

9368.02

10868.02

11288.71

20

21

22

PAY DAY

23

24

25

26

21

22

WS

-200

0

SAL

3200

0

LR

-6969.69

0

23

CF

-55

0

24

0

25

11033.71

14233.71

7264.02

26

27

28

29

PAY DAY

30

31

1

2

27

28

WS

-200

0

SAL

3000

0

29

CF

-50

0

30

0

31

7014.02

10014.02

32

2/01/1900

3/01/1900

Notes

33

MONTH INCOME:

MONTH EXPENSES:

MONTH PROFIT:

34

13120.69

-8106.67

5014.02

Sum

Cell Formulas
Range	Formula
F34	F34	=SUM(IF(FILTER(A2:N31,A1:N1="")>0,FILTER(A2:N31,A1:N1="")))
H34	H34	=SUM(IF(FILTER(A2:N31,A1:N1="")<0,FILTER(A2:N31,A1:N1="")))
J34	J34	=SUM(F39+H39)

DanteAmor · Jul 25, 2020

HektikSwift said:
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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

MON

TUE

WED

THU

FRI

SAT

SUN

2

29

30

1

PAY DAY

2

3

4

5

3

4

SAL

2000

0

PB

-100

0

5

0

6

0

7

7000

6900

8

6

7

8

PAY DAY

9

10

11

12

9

10

WS

-200

0

SAL

3000

0

AM

-11.99

0

11

CF

-50

0

12

0

13

6650

9650

9638.01

14

13

14

15

PAY DAY

16

17

18

19

15

16

WS

-200

NF

-19.99

SAL

1500

0

IR

420.69

17

CF

-50

0

18

0

19

9388.01

9368.02

10868.02

11288.71

20

21

22

PAY DAY

23

24

25

26

21

22

WS

-200

0

SAL

3200

0

LR

-6969.69

0

23

CF

-55

0

24

0

25

11033.71

14233.71

7264.02

26

27

28

29

PAY DAY

30

31

1

2

27

28

WS

-200

0

SAL

3000

0

29

CF

-50

0

30

0

31

7014.02

10014.02

32

02/01/1900

03/01/1900

Notes

33

MONTH INCOME:

MONTH EXPENSES:

MONTH PROFIT:

34

13120.69

-8106.67

5014.02

Hoja5

Cell Formulas
Range	Formula
F34	F34	=SUMPRODUCT((IFERROR((B4:N30)(B4:N30>0),0))(MOD(COLUMN(B4:N30),2)=0)*(MOD(INT((ROW(B4:N30)/3)-0.1),2)=1))
H34	H34	=SUMPRODUCT((IFERROR((B4:N30)(B4:N30<0),0))(MOD(COLUMN(B4:N30),2)=0)*(MOD(INT((ROW(B4:N30)/3)-0.1),2)=1))
J34	J34	=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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

MON

TUE

WED

THU

FRI

SAT

SUN

2

29

30

1

PAY DAY

2

3

4

5

3

4

SAL

2000

0

PB

-100

0

5

0

6

0

7

7000

6900

8

6

7

8

PAY DAY

9

10

11

12

9

10

WS

-200

0

SAL

3000

0

AM

-11.99

0

11

CF

-50

0

12

0

13

6650

9650

9638.01

14

13

14

15

PAY DAY

16

17

18

19

15

16

WS

-200

NF

-19.99

SAL

1500

0

IR

420.69

17

CF

-50

0

18

0

19

9388.01

9368.02

10868.02

11288.71

20

21

22

PAY DAY

23

24

25

26

21

22

WS

-200

0

SAL

3200

0

LR

-6969.69

0

23

CF

-55

0

24

0

25

11033.71

14233.71

7264.02

26

27

28

29

PAY DAY

30

31

1

2

27

28

WS

-200

0

SAL

3000

0

29

CF

-50

0

30

0

31

7014.02

10014.02

32

02/01/1900

03/01/1900

Notes

33

MONTH INCOME:

MONTH EXPENSES:

MONTH PROFIT:

34

13120.69

-8106.67

5014.02

Hoja5

Cell Formulas
Range	Formula
F34	F34	=SUM((IFERROR((A2:N30)(A2:N30>0),0))(A1:N1=""))
H34	H34	=SUM((IFERROR((A2:N30)(A2:N30<0),0))(A1:N1=""))
J34	J34	=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 · Jul 25, 2020

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 · Jul 25, 2020

HektikSwift said:
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.

HektikSwift said:
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 · Jul 26, 2020

Peter_SSs said:
=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!

Peter_SSs · Jul 26, 2020

You're welcome.

Help with "SUMIF" function

HektikSwift

New Member

Excel Facts

DanteAmor

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

HektikSwift

New Member

Peter_SSs

MrExcel MVP, Moderator

DanteAmor

Well-known Member

HektikSwift

New Member

Peter_SSs

MrExcel MVP, Moderator

HektikSwift

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Help with "SUMIF" function

New Member

Excel Facts

Well-known Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock