Trying to simplify IF AND Statement

[blittenb]

I am trying to conditionally format based on the following requirements.

I am trying to accomplish the following:
If C8 = 0 then SUM(C21:C23) must = 0

If C8 = 1 then SUM(C21:C23) must = 0


If C8 = 2 then SUM(C21:C23) must = 2
If C8 = 3 then SUM(C21:C23) must = 4
etc

I came up with this but I feel this is granularity is unnecessary.
=IF(AND(C8=0,SUM(C21:C23)=0),TRUE,FALSE)
=IF(AND(C8=1,SUM(C21:C23)=0),TRUE,FALSE)
=IF(AND(C8=2,SUM(C21:C23)=2),TRUE,FALSE)
=IF(AND(C8=3,SUM(C21:C23)=4),TRUE,FALSE)

Nordstrom - LR Survey - Template.xlsx
	B	C	D	E	F
8	-1	2	0	0	0
9	0	0	0	0	0
10	0	0	0	0	0
11	0	0	0	0	0
12	0	0	0	0	0
13	0	0	0	0	0
14	0	0	0	0	0
15	0	0	0	0	0
16	4	4	0	0	0
17	0	0	0	0	0
18	2	0	0	0	0
19	0	0	0	0	0
20	0	0	0	0	0
21	0	0	0	0	0
22	0	0	0	0	0
23	0	0	0	0	0
NSG Engineer

Cell Formulas
Range	Formula
C8	C8	='LR BOM'!B84
D8	D8	='LR BOM'!B127
E8	E8	='LR BOM'!B169
F8	F8	='LR BOM'!B214
C9	C9	='LR BOM'!$B$69+'LR BOM'!$B$73+'LR BOM'!$B$77+'LR BOM'!$B$81
D9	D9	='LR BOM'!B112+'LR BOM'!B116+'LR BOM'!B120+'LR BOM'!B124
E9	E9	='LR BOM'!B154+'LR BOM'!B158+'LR BOM'!B162+'LR BOM'!B166
F9	F9	='LR BOM'!B199+'LR BOM'!B203+'LR BOM'!B207+'LR BOM'!B211
C10	C10	='LR BOM'!$B$83
D10	D10	='LR BOM'!B126
E10	E10	='LR BOM'!B168
F10	F10	='LR BOM'!B213
C11:C14	C11	=$C$10
D11:F11	D11	=D10
D12:F12	D12	=D10
D13:F13	D13	=D10
D14:F14	D14	=D10
C16:F16	C16	=2*C8-C17-C15
B8	B8	='LR BOM'!$B$42-1
B9	B9	=('LR BOM'!$B$27+'LR BOM'!$B$31+'LR BOM'!$B$35+'LR BOM'!$B$39)
B10	B10	='LR BOM'!B41
B11:B14	B11	=$B$10
B15:B17	B15	=(0+C15+D15+E15+F15)
B18	B18	=2
B19	B19	=$B$10-B20
C19	C19	=$C$10-C20
D19:F19	D19	=D10-D20

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F8	Expression	=SUM(F21:F23)<>(F8*1)	text	NO
F8	Expression	=SUM(F21:F23)=(F8*1)	text	NO
D8	Expression	=SUM(D21:D23)<>(D8*1)	text	NO
D8	Expression	=SUM(D21:D23)=(D8*1)	text	NO
E8	Expression	=SUM(E21:E23)<>(E8*1)	text	NO
E8	Expression	=SUM(E21:E23)=(E8*1)	text	NO
C8	Expression	=SUM(C21:C23)<>(C8*1)	text	NO
C8	Expression	=SUM(C21:C23)=(C8*1)	text	NO
B8	Expression	=IF(B8>(B21+B22+B23),"true","false")	text	NO
B8	Expression	=SUM(B21:B23)=(B8*2)	text	NO
B8	Expression	=SUM(B21:B23)<>(B8*2)	text	NO
B15,B16:F16,B17	Cell Value	>4	text	NO
B21	Expression	=SUM(B21:B23)<>(B8*2)	text	NO
B21	Expression	=SUM(B21:B23)=(B8*2)	text	NO
B22	Expression	=SUM(B21:B23)<>(B8*2)	text	NO
B22	Expression	=SUM(B21:B23)=(B8*2)	text	NO
B23	Expression	=SUM(B21:B23)<>(B8*2)	text	YES
B23	Expression	=SUM(B21:B23)=(B8*2)	text	NO
B10	Cell Value	<>$D$28	text	NO
B10	Cell Value	=$D$28	text	NO
C10	Cell Value	=$D$29	text	NO
C10	Cell Value	<>$D$29	text	NO
D10	Cell Value	<>$D$30	text	NO
D10	Cell Value	=$D$30	text	NO
E10	Cell Value	<>$D$31	text	NO
E10	Cell Value	=$D$31	text	NO
F10	Cell Value	<>$D$32	text	NO
F10	Cell Value	=$D$32	text	NO

 

[Joe4]

You can simplify these:
=IF(AND(C8=0,SUM(C21:C23)=0),TRUE,FALSE)
=IF(AND(C8=1,SUM(C21:C23)=0),TRUE,FALSE)
=IF(AND(C8=2,SUM(C21:C23)=2),TRUE,FALSE)
=IF(AND(C8=3,SUM(C21:C23)=4),TRUE,FALSE)
to this, since AND already returns a boolean value:
=AND(C8=0,SUM(C21:C23)=0)
=AND(C8=1,SUM(C21:C23)=0)
=AND(C8=2,SUM(C21:C23)=2)
=AND(C8=3,SUM(C21:C23)=4)

 

[blittenb]

You can simplify these:
=IF(AND(C8=0,SUM(C21:C23)=0),TRUE,FALSE)
=IF(AND(C8=1,SUM(C21:C23)=0),TRUE,FALSE)
=IF(AND(C8=2,SUM(C21:C23)=2),TRUE,FALSE)
=IF(AND(C8=3,SUM(C21:C23)=4),TRUE,FALSE)
to this, since AND already returns a boolean value:
=AND(C8=0,SUM(C21:C23)=0)
=AND(C8=1,SUM(C21:C23)=0)
=AND(C8=2,SUM(C21:C23)=2)
=AND(C8=3,SUM(C21:C23)=4)

I combined them to further simplify but thank you.

=AND(C8=2,SUM(C21:C23)=2)=AND(C8=1,SUM(C21:C23)=0)=AND(C8=0,SUM(C21:C23)=0)

 

[Eric W]

I don't think that formula works right. Try the E21 formula here:

Book1
	C	D	E
8	3
9
10
11
12
13
14
15
16
17
18
19
20
21	0		TRUE
22	2		FALSE
23	2
Sheet2

Cell Formulas
Range	Formula
E21	E21	=LET(s,SUM(C21:C23),SWITCH(C8,0,s=0,1,s=0,2,s=2,3,s=4,FALSE))
E22	E22	=AND(C8=2,SUM(C21:C23)=2)=AND(C8=1,SUM(C21:C23)=0)=AND(C8=0,SUM(C21:C23)=0)