A more elegant countifs criteria than "$B6-$B5/1<0" across 50 columns

[tekram]

In economic conditions, the 50 US states each has a state by state economic activity. The percent change each month can be an indication of a coming recession.

So there are 50 columns that needed to be summed across each month (row) and we need to know how many states are going into a percent change < 0. This can be shown possibly as;

=SUM(COUNTIFS(B6:I6,{

"$B6-$B5/1<0","$C6-$C5/1<0","$D6-$D5/1<0","$E6-$E5/1<0","$F6-$F5/1<0","$G6-$G5/1<0","$H6-$H5/1<0","$I6-$I5/1<0",

}))

only the first 8 states shown.

So is there a way to sum this number each month in a formula without typing up all 50 absolute cell references?

Thank you from an Excel novice.

 

[bebo021999]

Assum 50 states are from colum B to AY

this count states with value in row 6 smaller than row 5:

=SUMPRODUCT(--(B6:AY6<B5:AY5))

 

[StephenCrump]

Welcome to the Forum!

Something like this perhaps:

	A	B	C	D	E	F	G
1		AL	AK	AZ	AR	CA	No down
2	1-Sep	100	105	103	110	106
3	1-Oct	99	104	102	111	105	4
4	1-Nov	101	106	101	109	107	2
5
Sheet1

Cell Formulas
Range	Formula
G3:G4	G3	=SUMPRODUCT(--(B3:F3<B2:F2))

 

[tekram]

Thank you both.

For some reason, countifs was the first formula that came up when I searched, not realizing there are other simpler and better solutions.