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 23 2022 01:34 PM - edited Nov 23 2022 01:58 PM
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
Nov 23 2022 02:03 PM
I think the formula is doing the right job. To the left of Q are 10 entries in the rows where there is also an entry in Q.
Nov 23 2022 06:26 PM
Nov 23 2022 07:05 PM
Nov 23 2022 07:26 PM
Nov 23 2022 08:16 PM
Nov 24 2022 05:31 AM
Nov 24 2022 05:31 AM
Nov 24 2022 06:19 AM
So I understand correctly? The entry in cell E20, i.e. the 10, must not be counted in column Q because there is also a 10 in cell Q20?
Nov 24 2022 11:27 AM
FILTER has removed the columns that are not APM.
This is how the formula is arriving at 10:
Nov 24 2022 12:20 PM
Nov 24 2022 12:46 PM
Please explain how you arrive at a count of 9. There are 10 entries in the screenshots. That is exactly where the problem lies. You claim there are 9 and no one can reconstruct this count.
Nov 24 2022 12:55 PM
Nov 24 2022 02:56 PM
@dscheikey Look down column Q. Each time you see a number in a cell in column Q (Q12:Q328) look to the left towards Jan. So for each number you encounter in column Q you are going to follow that row back to see if there are any other numbers in that same row. If there is a number in like Q220 and also a number in E220, G220, I220 or so on, the number in Q220 (even though it reflects an actual PM, it does not count against the total number trying to be achieved. Out of the 21 showing in the Q column, 9 of them have instances of other prior PMs having been performed. This leaves 12 PMs that have had only 1 PM at least and up to what ever month is current.
Nov 24 2022 03:59 PM
Please explain how you arrive at a count of 9. There are 10 entries in the screenshots. That is exactly where the problem lies. You claim there are 9 and no one can reconstruct this count.
Nov 24 2022 04:38 PM
Nov 24 2022 05:24 PM
Nov 24 2022 05:43 PM
Nov 24 2022 06:04 PM
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))
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