Nov 23 2022 07:54 AM - edited Nov 23 2022 09:47 AM
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)-SUMPRODUCT(($C12:O328<>"")*(Q12:Q328<>"")*(MOD(COLUMN($C12:O328),2)=1))&"/"&COUNTA(Q12:Q328)&"/"&SUMPRODUCT(($C12:O328<>"")*(Q12:Q328<>"")*(MOD(COLUMN($C12:O328),2)=1))
When this formula executes I'm getting a result of 11/21/10. It should be 12/21/9. I'm trying to figure out how to write this correctly or change it all together to gain the proper results. It looks like the issue is stemming from ROW 220. I'm trying to Counta() all the entries in column "Q", Q12:Q328 (The middle #) and then see if other entries exist within the same row in prior months under specific columns relating to APM's. If an entry in column "Q" also shows an entry in the same row prior to this "Q" entry, this entry would subtract from the Counta() count and plot the result of the subtractions to the left of the center number then plot the number of times from within the column, the entry had to be subtracted. This number would be plotted to the right of the center number. The issue is the formula is including a prior entry throwing off my numbers. Cell Q10 is where the formula is that I am having issues with but I'm sure the others in cells E10, G10, I10, K10, M10, O10, S10, U10, W10 & Y10 will have the same issue when and if the same condition ever exists . I have attached my workbook so please check this out and let me know if you have a way to fix this. This workbook represents buildings I manage as can be seen in column A. Each building requires at least 1 APM (Annual Preventive Maintenance) check so when more than 1 is conducted I need to subtract any duplicates out of the counta() result and show the ones that actually count to the left of the counta() result. Is there anyone that can help me figure this out?? Like I mentioned, the numbers that should be showing in Q10 are 12/21/9.
Nov 24 2022 06:30 PM
Nov 24 2022 06:43 PM
Nov 25 2022 05:05 PM
Nov 26 2022 07:17 AM
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))
Nov 26 2022 08:45 AM
SolutionHi 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