Forum Discussion
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 simplified the SUMPRODUCT with FILTER and LET. FILTER allows me to remove the COLUMN/MOD criteria from the SUMPRODUCT..
In testing, I kept it simple and tried to return the desired 12:
=LET(data,C12:O328,header,C11:O11,q,Q12:Q328,apm,FILTER(data,header="APM"),COUNTA(q)-SUMPRODUCT((apm<>"")*(q<>"")))
The above returns 11. I added a filter to your data and checked the results manually and came up with 11 again.
Edit:
I re-did the entire formula for Q10:
=LET(data,C12:O328,header,C11:O11,q,Q12:Q328,cq,COUNTA(q),apm,FILTER(data,header="APM"),s,SUMPRODUCT((apm<>"")*(q<>"")),cq-s&"/"&cq&"/"&s)Result: 11/21/10
So I double checked my count by looking at the number of cells in column Q for which Counta(q12:q328) equals 21. Of the 21 I looked to the left across the cells to see if any other entry's were in the same row of each building and I manually counted 9. So, 21-9 = 12. So the numbers in cell Q10 should show as follows: 12/21/9. 12 of the 21 are new never performed PMs and 9 are PMs that were performed previously. So, the numbers you say are being returned are incorrect and this is why I am reaching out for help with this.
