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
Hello Sir, I sincerely appreciate the help you re giving me to seek a resolution for my issue. I don't know yet if the formula you wrote will give me the answers I am looking for I copied and pasted it into cell Q10 and end up with #NAME? And I also don't know if the version I Have of 365 Enterprise is compatible with the code. You may have the answers I am looking for but I await your response. If it does do what I need and it is compatible could I ask that you formulate so I end up with 12/21/9. Its super nice to find people who have the knowledge and know how and are willing to help others such as my. My knowledge is limited and I do the best I can but sometimes my best is simply not good enough. If you are someone who resides here in the US I want to wish you and yours a Happy Thanksgiving. This to to all others willing to give assistance when assistance is needed. Than you
I think this one will do it. This is a situation where being on the Semi-Annual update channel is bad luck because there's a lot more functions available in the Current update channel (and Insider's, for that matter). Semi-Annual lacks functions for handling arrays so we must make do:
=LET(nonblanks,Q12:Q328,One,C12:C328,Two,E12:E328,Three,G12:G328,Four,I12:I328,Five,K12:K328,Six,M12:M328,Seven,O12:O328,total,SUM(FILTER(One,(LEN(One&Two&Three&Four&Five&Six&Seven)>0)*(nonblanks<>""))),b,COUNTA(nonblanks),TEXTJOIN("/",,total,b,b-total))