Forum Discussion
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)-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.
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?
- The issue is that there are 9 instances of PMs conducted in Aug whereby prior PMs were conducted, It does not matter how many, what matters is the simple fact that at least one or more PMs were conducted prior to the one in Q in this example. So, the confusion is that the formula is counting the number of PMs within the array and that is not the result I am looking for.
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
- Ok. I see what you are talking about, Here is where the issue is. You are counting the physical entry's within the array. The deal here is not to count the number of physical entry's. The issue here is to realize how many of the entries in Q also have an entry prior to the one shown in I am not concerned with how PMs fall with in the array, Only the number of PMs in Q that have had a prior PM conducted on it. Lets say 1 property had a PM done every month. I'm not looking for the count, only to realize that at least 1 PM was performed prior to the current month. So in this case, even if there were 4 or 5 more entry's for an individual property, the count for this property will still be 1 because this entry in Q shows prior entry's.
- I have Windows 10 Enterprise. Will this formula work with windows 10?
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?
- Remember, I'm only looking for how many PMs were conducted, in this case, in Aug Column Q, and checking to see if PMs were conducted on any of the buildings previously. The whole point is to get a count on how many of the PMs were original/1st time PMs as at least 1 pm per building has been conducted over the course of 12 months. So if multiple PMs were performed on any individual building all i care about is to account for just 1 of the PMs that were performed. Where 11 is coming from I have no idea much less I do not know where you are getting the 2 extra PMs from? I checked this a number of times and I only get 9 of the 21 that were previously PM'ed.
- So I double checked my count by looking at the number of cells in column Q for which Counta(q12:q328) equals 21. Of the 21 I looked to the left across the cells to see if any other entry's were in the same row of each building and I manually counted 9. So, 21-9 = 12. So the numbers in cell Q10 should show as follows: 12/21/9. 12 of the 21 are new never performed PMs and 9 are PMs that were performed previously. So, the numbers you say are being returned are incorrect and this is why I am reaching out for help with this.
