Count but only on last 4 columns of dynamic range

[Buzzcut]

Morning,

I've got most of my formula working, I just can't see limit the function to the last 4 columns of the range.

I'm adding 2 new columns per day and only want the sum of the last 4 on a rolling basis. The logic is that as time goes on there should be fewer counts for PERM over the last 2 days and the counts should all go to 0.

The "Count All PERM" can easily be done with =SUMIF($L$2:$S$2,"*PERM*",$L3:$S3) but the result in Col C is what I'm after.
I've been trying COLUMNS and INDEX but they all need a specific range, and my range keeps extending every day.

I can find the number of the last column (11) by using =LOOKUP(2,1/ (1:1<>""),ROW(A:A)) but I'm not sure how to pass this to the range.

Any ideas, folks?

Thanks...Buzz

 

[maabadi]

Use this Both UDF.
1. first for Count ALL Perm
2. For Count 4 Last Column

Public Function CntAll(Rng As Range) As Long
Dim i As Long, Lc As Long
Lc = Cells(1, Columns.Count).End(xlToLeft).Column
CntAll = Application.WorksheetFunction.SumIf(Range(Cells(1, 4), Cells(1, Lc)), "Perm*", Range(Cells(Rng.Row, 4), Cells(Rng.Row, Lc)))
End Function

Public Function CntL4CL(Rng As Range) As Long
Dim i As Long, Lc As Long
Lc = Cells(1, Columns.Count).End(xlToLeft).Column
CntL4CL = Application.WorksheetFunction.SumIf(Range(Cells(1, Lc - 3), Cells(1, Lc)), "Perm*", Range(Cells(Rng.Row, Lc - 3), Cells(Rng.Row, Lc)))
End Function

After Input them in VBA. Come Back to excel window and write Function name and Select Range that you want Count
Example:

=CntAll(D2:O2)

=CntL4CL(D2:O2)

 

[Buzzcut]

Thanks, but I should have mentioned this needs to be done with Formulas rather than VBA.

I've got the SUM working for the last 4 columns but not for only when the heading is PERM

=SUM(OFFSET(D2,0,COUNTA($D$1:$XFD$1)-1,1,-4))

 

[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’)

 

[Snakehips]

@Buzzcut

Maybe this in C2

=SUMPRODUCT(OFFSET(D2,0,COUNTA($D$1:$XFD$1)-1,1,-4)*(LEFT(OFFSET(D$1,0,COUNTA($D$1:$XFD$1)-1,1,-4),4)="PERM"))

Hope that helps

 

[Buzzcut]

@Snakehips - Amazing, that works great.

 

[Peter_SSs]

@Snakehips - Amazing, that works great.

But, unfortunately, still no record of what version(s) you are using.

For example, if Excel 365 and the LET function, it could be done like this without the multiple uses of the volatile function OFFSET.
Adjust ranges if needed of course.

21 05 11.xlsm
	C	D	E	F	G	H	I	J	K	L	M
1		ALS 8	PERM 8	ALS 9	PERM 9	ALS 10	PERM 10	ALS 11	PERM 11
2	0	1	1	1	1	1		1
3	2	1	1	1	1	1	1	1	1
4	1	1	1		1		1
Sum in Last 4

Cell Formulas
Range	Formula
C2:C4	C2	=LET(seq,SEQUENCE(,4,COUNTA(D$1:AY$1),-1),SUM(FILTER(INDEX(D2:AY2,seq),LEFT(INDEX(D$1:AY$1,seq),1)="P")))