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
Remember, I'm only looking for how many PMs were conducted, in this case, in Aug Column Q, and checking to see if PMs were conducted on any of the buildings previously. The whole point is to get a count on how many of the PMs were original/1st time PMs as at least 1 pm per building has been conducted over the course of 12 months. So if multiple PMs were performed on any individual building all i care about is to account for just 1 of the PMs that were performed. Where 11 is coming from I have no idea much less I do not know where you are getting the 2 extra PMs from? I checked this a number of times and I only get 9 of the 21 that were previously PM'ed.
