Count Each Row In Dynamic Range Once If Any Cell > 0

[smokeyclimber]

I have a data set with unique IDs in column B and dates in row 2. I am trying to make a formula that looks at a date I've selected, finds that column in the data sheet, looks at at a range that includes the found column and the 11 before it (i.e., a whole year), then counts the number of rows that have a value > 0 in that range.

I have a formula right now that returns the raw count, but I'm struggling with how to only count a row once.

The formula I started with is below.

=COUNTIFS(INDIRECT(CONCATENATE("Data!",LEFT(INDEX(Data!$A$1:$DK$2,1,MATCH(B$1,Data!$A$2:$DK$2,0)),3),":",LEFT(OFFSET(INDEX(Data!$A$1:$DK$2,1,MATCH(B$1,Data!$A$2:$DK$2,0)),0,-12),3))),">"&0)-2

If there was a ROWSIF function, I'd just use that.

Does anyone know how to count the rows only once without making a helper sheet?

 

[KRice]

Try this and let me know if it does what you want. I've placed the data and your input/output on the same sheet, so you'll need to split those and add sheet references. Input date in AA1 and count of rows containing some value >0 in AB1...and that meant I needed to move the unique ID from B to some other column (AA). Those edits should be straightforward to fit your table structure. What should happen if 11 columns to the left of the matching column are not available...or is that not possible when you select an input date?

MrExcel_20220610.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1																											5/1/2022	6
2	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23
3																	1										1
4				2																							3
5					1																						5
6						3																					7
7																											9
8																											10
9									1																		8
10										2																	6
11											4			3													4
12												5					6										2
Sheet8

Cell Formulas
Range	Formula
AB1	AB1	=SUMPRODUCT(--(MMULT(--(OFFSET(INDEX($A$3:$Z$12,,MATCH($AA$1,$A$2:$Z$2,0)),,-11,COUNTA($AA$3:$AA$12),12)>0),{1;1;1;1;1;1;1;1;1;1;1;1})>0))
B2:Z2	B2	=EDATE(A2,1)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)