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 really hope you have Excel 365! I understand the counts you're looking for and this formula gets the desired results for Q10.
=LET(data,C12:O328,header,C11:O11,vector,Q12:Q328,nonblank,COUNTA(vector),f,FILTER(data,header="apm"),b,SUM(BYROW(HSTACK(f,vector),LAMBDA(row,IF(AND(TAKE(row,,-1)<>"",SUM(DROP(row,,-1))>0),1,0)))),TEXTJOIN("/",,nonblank-b,nonblank,b))
I copied and pasted this formula into cell Q10 and it gives me #NAME?