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))
- 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))
- I don't see any "/" in the formula so will it still show as 12/21/9 in cell Q10. Please look at the workbook I attached to the original post as it shows how things are to look and also shows all the other formulas that are tied into this and so on. Also if you look at cell T2 you will see how Header "APM" can be other Header names based on what is selected for T2. This is a versatile Workbook that tracks various things. This is why formulas found in row 3 are the way they are written. Just want to put that out there. A worthy mention I will say.
- I copied and pasted this formula into cell Q10 and it gives me #NAME?
- I have Office 365 Apps for Enterprise
