Forum Discussion
Formula Help
Hi Carl, now I understand.
My alternative formula looks like this: For Q10
=COUNTA(Q12:Q328)-SUMPRODUCT(--(Q12:Q328<>0),--(MMULT(--($C12:P328<>"")*ISODD(COLUMN($C:P)),SEQUENCE(COLUMN()-3))<>0))&"/"&COUNTA(Q12:Q328)&"/"&SUMPRODUCT(--(Q12:Q328<>0),--(MMULT(--($C12:P328<>"")*ISODD(COLUMN($C:P)),SEQUENCE(COLUMN()-3))<>0))or with LET():
=LET(in,SUMPRODUCT(--(Q12:Q328<>0),--(MMULT(--($C12:P328<>"")*ISODD(COLUMN($C:P)),SEQUENCE(COLUMN()-3))<>0)),COUNTA(Q12:Q328)-in&"/"&COUNTA(Q12:Q328)&"/"&in)Then you can copy them from there to the other places. Please not in column C
dscheikey Look down column Q. Each time you see a number in a cell in column Q (Q12:Q328) look to the left towards Jan. So for each number you encounter in column Q you are going to follow that row back to see if there are any other numbers in that same row. If there is a number in like Q220 and also a number in E220, G220, I220 or so on, the number in Q220 (even though it reflects an actual PM, it does not count against the total number trying to be achieved. Out of the 21 showing in the Q column, 9 of them have instances of other prior PMs having been performed. This leaves 12 PMs that have had only 1 PM at least and up to what ever month is current.
Please explain how you arrive at a count of 9. There are 10 entries in the screenshots. That is exactly where the problem lies. You claim there are 9 and no one can reconstruct this count.
- There are 9 PMs within the 21 PMs performed in the month of AUG, column Q that have had prior PMs performed on the same building. So take the 9 away from the 21 and we have 12 buildings of the 21 that had their 1st PM done on them.