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

 

[HektikSwift]

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)

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?

 

[Peter_SSs]

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]

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

 

[Peter_SSs]

Yeah, i figured this would be the best. Thanks mate

 

[ebea]

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!

Dropbox

www.dropbox.com

 

[DanteAmor]

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]

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]

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!

Dropbox

www.dropbox.com

Not working for me