Forum Discussion
Solved
Formula Help
Hello Tech Community, I can really use some help. I am using the SUMPRODUCT Function (maybe its the wrong function) and I'm not getting the proper results. Here is the Formula: =COUNTA(Q12:Q328)-SU...
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
I think the formula is doing the right job. To the left of Q are 10 entries in the rows where there is also an entry in Q.
Ok. I see what you are talking about, Here is where the issue is. You are counting the physical entry's within the array. The deal here is not to count the number of physical entry's. The issue here is to realize how many of the entries in Q also have an entry prior to the one shown in I am not concerned with how PMs fall with in the array, Only the number of PMs in Q that have had a prior PM conducted on it. Lets say 1 property had a PM done every month. I'm not looking for the count, only to realize that at least 1 PM was performed prior to the current month. So in this case, even if there were 4 or 5 more entry's for an individual property, the count for this property will still be 1 because this entry in Q shows prior entry's.