Help with SUM function

[excelnoob123123]

Hi, I have illustrated here what I am trying to do
I am trying to make a formula for "max days" and "recent consecutive days". Columns C and beyond are from a pivot table, whereas A and B cells are within that sheet. So eventually, columns J, K, L, and so on will be added and I need a function that will match this when the pivot table recalls information from new entries

Thank you so much for your time. Had a headache trying to figure this out on my own spending over 5 hours that I decided its best I ask for help before I go crazy

 

[jasonb75]

Maybe in A2 and filled down

=COUNT(D2:XFD2)

 

[ravi2628]

Book1
	A	B	C	D	E	F	G	H
1	4	3		1		1	1	1
Sheet1

Cell Formulas
Range	Formula
A1	A1	=COUNTA(D1:XFD1)
B1	B1	=MAX(FREQUENCY(IF(D1:XFD1>0,COLUMN(D1:XFD1)),IF(D1:XFD1=0,COLUMN(D1:XFD1))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Please try this.

 

[excelnoob123123]

Book1
	A	B	C	D	E	F	G	H
1	4	3		1		1	1	1
Sheet1

Cell Formulas
Range	Formula
A1	A1	=COUNTA(D1:XFD1)
B1	B1	=MAX(FREQUENCY(IF(D1:XFD1>0,COLUMN(D1:XFD1)),IF(D1:XFD1=0,COLUMN(D1:XFD1))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Please try this.

Hi it works but the function fails for "evan" as the recent consecutive days should be 0

 

[ravi2628]

Hi it works but the function fails for "evan" as the recent consecutive days should be 0

Means you want it from the next consecutive entries of the list.

 

[excelnoob123123]

Means you want it from the next consecutive entries of the list.

Hi I am not entirely sure what that means. But since the most recent cell is blank I don't want it to count

 

[jasonb75]

This assumes that the dates in row 1 will not be entered in advance. If they are then there is no way of identifying whether or not the most recent cell is blank unless we work to a specific date.
If you are not using office 365 then you may need to array confirm the formulas in column B with Ctrl Shift Enter.

Book2
	A	B	C	D	E	F	G	H	I
1	Max	Recent	Name	01-Sep	02-Sep	03-Sep	04-Sep	05-Sep	06-Sep
2	4	3	a	1			1	1	1
3	3	0	b			1	1	1
4	5	4	c	1		1	1	1	1
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=SUMIF($1:$1,">0",2:2)
B2:B4	B2	=LOOKUP(1E+100,FREQUENCY(IF($D2:INDEX(2:2,MATCH(1E+100,$1:$1))<>"",$D$1:INDEX($1:$1,MATCH(1E+100,$1:$1))),IF($D2:INDEX(2:2,MATCH(1E+100,$1:$1))="",$D$1:INDEX($1:$1,MATCH(1E+100,$1:$1)))))