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 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
I have Windows 10 Enterprise. Will this formula work with windows 10?