Help with "SUMIF" function

[HektikSwift]

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.

=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]

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]

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]

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		$0.00	PB	-$100.00		$0.00		PB	Phone Bill
5						$0.00		$0.00		$0.00		$0.00		$0.00		NF	Netflix
6						$0.00		$0.00		$0.00		$0.00		$0.00		AM	Apple Music
7					$7,000.00		$7,000.00		$7,000.00		$6,900.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		$0.00		LR	Loan Repayments
11	CF	-$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
14	13		14		15	PAY DAY	16		17		18		19
15
16	WS	-$200.00	NF	-$19.99	SAL	$1,500.00		$0.00		$0.00		$0.00	IR	$420.69
17	CF	-$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
20	20		21		22	PAY DAY	23		24		25		26
21
22	WS	-$200.00		$0.00	SAL	$3,200.00		$0.00		$0.00	LR	-$6,969.69		$0.00
23	CF	-$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
26	27		28		29	PAY DAY	30		31		1		2
27
28	WS	-$200.00		$0.00	SAL	$3,000.00		$0.00		$0.00
29	CF	-$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
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]

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		0	PB	-100		0
5						0		0		0		0		0
6						0		0		0		0		0
7					7000		7000		7000		6900		6900
8	6		7		8	PAY DAY	9		10		11		12
9
10	WS	-200		0	SAL	3000		0	AM	-11.99		0		0
11	CF	-50		0		0		0		0		0		0
12		0		0		0		0		0		0		0
13	6650		6650		9650		9650		9638.01		9638.01		9638.01
14	13		14		15	PAY DAY	16		17		18		19
15
16	WS	-200	NF	-19.99	SAL	1500		0		0		0	IR	420.69
17	CF	-50		0		0		0		0		0		0
18		0		0		0		0		0		0		0
19	9388.01		9368.02		10868.02		10868.02		10868.02		10868.02		11288.71
20	20		21		22	PAY DAY	23		24		25		26
21
22	WS	-200		0	SAL	3200		0		0	LR	-6969.69		0
23	CF	-55		0		0		0		0		0		0
24		0		0		0		0		0		0		0
25	11033.71		11033.71		14233.71		14233.71		14233.71		7264.02		7264.02
26	27		28		29	PAY DAY	30		31		1		2
27
28	WS	-200		0	SAL	3000		0		0
29	CF	-50		0		0		0		0
30		0		0		0		0		0
31	7014.02		7014.02		10014.02		10014.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]

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		0	PB	-100		0
5						0		0		0		0		0
6						0		0		0		0		0
7					7000		7000		7000		6900		6900
8	6		7		8	PAY DAY	9		10		11		12
9
10	WS	-200		0	SAL	3000		0	AM	-11.99		0		0
11	CF	-50		0		0		0		0		0		0
12		0		0		0		0		0		0		0
13	6650		6650		9650		9650		9638.01		9638.01		9638.01
14	13		14		15	PAY DAY	16		17		18		19
15
16	WS	-200	NF	-19.99	SAL	1500		0		0		0	IR	420.69
17	CF	-50		0		0		0		0		0		0
18		0		0		0		0		0		0		0
19	9388.01		9368.02		10868.02		10868.02		10868.02		10868.02		11288.71
20	20		21		22	PAY DAY	23		24		25		26
21
22	WS	-200		0	SAL	3200		0		0	LR	-6969.69		0
23	CF	-55		0		0		0		0		0		0
24		0		0		0		0		0		0		0
25	11033.71		11033.71		14233.71		14233.71		14233.71		7264.02		7264.02
26	27		28		29	PAY DAY	30		31		1		2
27
28	WS	-200		0	SAL	3000		0		0
29	CF	-50		0		0		0		0
30		0		0		0		0		0
31	7014.02		7014.02		10014.02		10014.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		0	PB	-100		0
5						0		0		0		0		0
6						0		0		0		0		0
7					7000		7000		7000		6900		6900
8	6		7		8	PAY DAY	9		10		11		12
9
10	WS	-200		0	SAL	3000		0	AM	-11.99		0		0
11	CF	-50		0		0		0		0		0		0
12		0		0		0		0		0		0		0
13	6650		6650		9650		9650		9638.01		9638.01		9638.01
14	13		14		15	PAY DAY	16		17		18		19
15
16	WS	-200	NF	-19.99	SAL	1500		0		0		0	IR	420.69
17	CF	-50		0		0		0		0		0		0
18		0		0		0		0		0		0		0
19	9388.01		9368.02		10868.02		10868.02		10868.02		10868.02		11288.71
20	20		21		22	PAY DAY	23		24		25		26
21
22	WS	-200		0	SAL	3200		0		0	LR	-6969.69		0
23	CF	-55		0		0		0		0		0		0
24		0		0		0		0		0		0		0
25	11033.71		11033.71		14233.71		14233.71		14233.71		7264.02		7264.02
26	27		28		29	PAY DAY	30		31		1		2
27
28	WS	-200		0	SAL	3000		0		0
29	CF	-50		0		0		0		0
30		0		0		0		0		0
31	7014.02		7014.02		10014.02		10014.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]

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]

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]

=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]

You're welcome.