COUNTIFS Formula Error

[kitm]

Hello,

I am trying to set up a formula to count the number of months a location was compliant with a metric target. I am trying to use the COUNTIFS formula but I keep getting a #VALUE error.

The Formula I am using is =COUNTIFS(D4:F4,">-7",D4:F4,"<7",G4:O4,">-5",G4:O4,"<5")

If I COUNTIFS the two ranges independantly ie =COUNTIFS(G3:O3,">-5",G3:O3,"<5") or =COUNTIFS(D3:F3,">-7",D3:F3,"<7") they work fine.

Is there another way to combine these two, or a better formula for counting this? I have two ranges in the row because the target metric is different from January - March (+/- 7) than April-December (+/- 5)

2023 Optimus Prime Tracking.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Location	January	Febrary	March	April	May	June	July	August	September	October	November	December	Total Months Compliant
3	Christown	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	0.00	0.00	2
4	Bethany Home	3.21	(2.27)	(0.35)	0.33	(19.52)	(0.00)	(17.43)	(31.50)	(12.81)	0.00	(5.53)	(8.63)	#VALUE!
5	Scottsdale	0.00	(0.40)	(5.80)	1.00	(13.50)	3.33	(0.08)	(7.00)	(20.20)	0.00	0.00	0.00
Teller OS

 

[skillilea]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Location	January	Febrary	March	April	May	June	July	August	September	October	November	December	Total Months Compliant
3	Christown	1	1	1		1			1			1		6
4	Bethany Home												1	1
5	Scottsdale		1	1						1	1			4
Sheet1

Cell Formulas
Range	Formula
N3:N5	N3	=SUMIFS($B3:$M3, $B3:$M3, ">0")

 

[Fluff]

I would just use

=COUNTIFS(G3:O3,">-5",G3:O3,"<5")+COUNTIFS(D3:F3,">-7",D3:F3,"<7")

 

[kitm]

I would just use

=COUNTIFS(G3:O3,">-5",G3:O3,"<5")+COUNTIFS(D3:F3,">-7",D3:F3,"<7")

Thank you that worked. I knew it would be something silly simple.

 

[Fluff]

You're welcome & thanks for the feedback.