To use countif in a dynamic column using dynamic row range

[Vimaldsouza]

I have a data set of employees with their Present absent details. I want to use countif of A and P in the last column of the data set. I am not sure in which column the last data will be present as sometimes we take 2 months data etc. after finding the last column I want to add a coutif in all the cells row wise for each employee. Eg of data set is as below
Employee No. Name 1st 2nd 3rd 4th 5th 6th
1 Ap P P A P WO WO

2 Ba A A P P P P

Please help

 

[arthurbr]

As you are only counting non empty cells you can use any range as long as it's larger than the actual one =countif(1:1,"p") will always work

 

[Vimaldsouza]

As you are only counting non empty cells you can use any range as long as it's larger than the actual one =countif(1:1,"p") will always work

Thanks for your reply but I wanted this in VBA 1:1 needs to be dynamic and also the row end is unknown

 

[Nishant94]

Sub CountingPresents()


Dim lastcol As Integer
Dim lastrow As Double


lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, 1).End(xlUp).Row


Range(Cells(2, lastcol + 1), Cells(lastrow, lastcol + 1)).Select


Selection.FormulaR1C1 = "=COUNTIF(RC[" & (-lastcol + 2) & "]:RC[-1],""p"")"


End Sub

 

[Vimaldsouza]

Thanks a lot it works perfectly