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 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
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.
- I think the confusion with is, and this is why I question the use if the SUMPRODUCT() function as I am not trying to count the total PMs conducted before amongst all buildings. I am just to look at each row per the building in that row, to see if a or any PMs have been done prior to the PM being performed in the Q column (in this case). Each time we move into another month we are look to the prior month(s) per building per row. So Jan does not look into Dec of the prior year, Jan just holds days that a PM was performed. This in essence is the staring point. We move to Feb and now we look back into Jan to see if a prior PM has been done for each building. Now in March we look back into Jan & Feb to see if a or any PMs have been performed. Now we move to Apr and look back into Jan, Feb & Mar. As we move forward we just include each month that has passed. So the bottom line is I may be using the wrong function for what I am trying to do. This same formula for column Q when figured out will also have to be applied to the other columns I mentioned and all the way to the Y column. That is the formula will have to entered into each cell in row 10 that is filled with orange and says APM. I hope this better explains what I am trying to achieve.