Simplified Formula

[tdk1964]

Hi,

Is there an easier or better way to sum the row and columns than these.
Rows
=IFERROR(MROUND(((IF(COUNT(C2:D2)=2,(D2-C2),0))+(IF(COUNT(E2:F2)=2,(F2-E2),0))+(IF(COUNT(G2:H2)=2,(H2-G2),0))+(IF(COUNT(I2:J2)=2,(J2-I2),0))+(IF(COUNT(K2:L2)=2,(L2-K2),0))+(IF(COUNT(M2:N2)=2,(N2-M2),0))+(IF(COUNT(O2:P2)=2,(P2-O2),0)))*24,0.1),0)

Columns
=IFERROR(MROUND((
IF(COUNT(C2:C2)=2,(D2-C2),0)+
IF(COUNT(C3:C3)=2,(D3-C3),0)+
IF(COUNT(C4:C4)=2,(D4-C4),0)+
IF(COUNT(C5:C5)=2,(D5-C5),0)+
IF(COUNT(C6:C6)=2,(D6-C6),0)+
IF(COUNT(C7:C7)=2,(D7-C7),0)+
IF(COUNT(C8:C8)=2,(D8-C8),0))*24,0.1),0)

 

[Scott Huish]

Instead of just showing your formula, can you explain in words what you are trying to do?

 

[Joe4]

Just a note that this formula looks problematic to me:

Columns
=IFERROR(MROUND((
IF(COUNT(C2:C2)=2,(D2-C2),0)+
IF(COUNT(C3:C3)=2,(D3-C3),0)+
IF(COUNT(C4:C4)=2,(D4-C4),0)+
IF(COUNT(C5:C5)=2,(D5-C5),0)+
IF(COUNT(C6:C6)=2,(D6-C6),0)+
IF(COUNT(C7:C7)=2,(D7-C7),0)+
IF(COUNT(C8:C8)=2,(D8-C8),0))*24,0.1),0)

All your COUNT formulas, are only counting one cell (C2:C2 is just one cell, C3:C3 is just one cell, etc). So none of them can ever equal 2 (you can never count 1 cell and get 2!)

 

[tdk1964]

Hi,
Thanks for the responses.
@Joe4 Yeah, I got that formula a bit wrong.
@Scott Huish You right, here's a better description.
What I'm trying to do is sum the number of hours for a column and row (there will be a lot of rows).
For example, Name 1 on Monday worked from 11 am to 11pm (12 hrs) so Mondays total is 12 (there are no other workers on Monday) and for the week it's 55.
Whilst my formula works it's very time consuming

Shift Planner.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Mon 30 Oct		Tue 31 Oct		Wed 01 Nov		Thu 02 Nov		Fri 03 Nov		Sat 04 Nov		Sun 05 Nov		Total
2	Section 1	Name 1	11:00 -	23:00			11:00 -	23:00	11:00 -	23:00	11:00 -	23:00	11:00 -	15:00	11:00 -	14:00	55
3		Name 2					13:00 -	17:00									4
4		Name 3					17:00 -	23:00			17:00 -	23:00					12
5		Name 4							12:00 -	23:00							11
6		Name 5															-
7		Total Hours	12		0		22		23		18		4		3		82
Schedule (2)

Cell Formulas
Range	Formula
Q2:Q6	Q2	=IFERROR(MROUND(((IF(COUNT(C2:D2)=2,(D2-C2),0))+(IF(COUNT(E2:F2)=2,(F2-E2),0))+(IF(COUNT(G2:H2)=2,(H2-G2),0))+(IF(COUNT(I2:J2)=2,(J2-I2),0))+(IF(COUNT(K2:L2)=2,(L2-K2),0))+(IF(COUNT(M2:N2)=2,(N2-M2),0))+(IF(COUNT(O2:P2)=2,(P2-O2),0)))*24,0.1),0)
C7	C7	=IFERROR(MROUND((IF(COUNT(C2:D2)=2,(D2-C2),0)+IF(COUNT(C3:D3)=2,(D3-C3),0)+IF(COUNT(C4:D4)=2,(D4-C4),0)+IF(COUNT(C5:D5)=2,(D5-C5),0)+IF(COUNT(C6:D6)=2,(D6-C6),0))*24,0.1),0)
E7,G7,I7,K7,M7,O7	E7	=IFERROR(MROUND(( IF(COUNT(E2:F2)=2,(F2-E2),0)+ IF(COUNT(E3:F3)=2,(F3-E3),0)+ IF(COUNT(E4:F4)=2,(F4-E4),0)+ IF(COUNT(E5:F5)=2,(F5-E5),0)+ IF(COUNT(E6:F6)=2,(F6-E6),0))*24,0.1),0)
Q7	Q7	=SUM(Q2:Q6)

 

[kevin9999]

Is there an easier or better way to sum the row and columns than these

Not sure if this is better but IMO it's simpler. Hope the formats show OK with the XL2BB

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Mon 30 Oct		Tue 31 Oct		Wed 01 Nov		Thu 02 Nov		Fri 03 Nov		Sat 04 Nov		Sun 05 Nov		Total
2	Section 1	Name 1	11:00	23:00			11:00	23:00	11:00	23:00	11:00	23:00	11:00	15:00	11:00	14:00	55
3		Name 2					13:00	17:00									4
4		Name 3					17:00	23:00			17:00	23:00					12
5		Name 4							12:00	23:00							11
6		Name 5															-
7		Total Hours	12		0		22		23		18		4		3		82
Sheet1

Cell Formulas
Range	Formula
Q2:Q6	Q2	=SUM(D2,F2,H2,J2,L2,N2,P2)-SUM(C2,E2,G2,I2,K2,M2,O2)
C7,E7,G7,I7,K7,M7,O7	C7	=SUM(D2:D6)-SUM(C2:C6)
Q7	Q7	=SUM(Q2:Q6)