Formula Help

[Carl_61]

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.

[#2]

@Carl_61 

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

[#3]

@Carl_61 

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.

[#4]

Thank you for checking out the formula. For some reason I cannot see the entire formula change.

[#5]

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.

[#6]

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.

[#7]

Thank you for your response to my post. The issue here is that out of the 21 in column Q (Q12:Q328) there are only 9 PMs of the 21 that were already PM'd in the month of Aug. This means only 12 of the Pms were 1st time Pms making 9 of them PMs that were previously conducted. I conducted a manual comparison like 8 times now and I still get 9 that were previously PM'd, 21 total, and 12 that were never PM'd before. So the numbers in cell Q10 should show as: 12/21/9.

[#8]

I have Windows 10 Enterprise. Will this formula work with windows 10?

[#9]

I have Windows 10 Enterprise. Will this formula work with windows 10?

[#10]

@Carl_61 

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?

[#11]

@Carl_61 

FILTER has removed the columns that are not APM.

This is how the formula is arriving at 10:

[#12]

1st of all just so you know the numbers showing in the cells are the day of the month the PM was performed. Thus being said I am not adding all those numbers together, just counting vertically how many cells have a day recorded in them, In the case of the column in question, "Q" (even though each column is doing the same thing) the result is not producing the correct result. If you look at column Q (Q12:Q328) you will see that there were 21 PMs recorded vertically, Now if you look to the left, across the row of cells back towards January where a day number had been recorded into a Q column, if there is a day number in any of the cells along the same row before the entry in the Q column, the Q column entry is to be subtracted from the vertical count in the Q column. After going from the top to the bottom of the Q column and counting all PMs that have been conducted before you will count 9. So, of the 21 of the vertical count there are 9 of the 21 that have been PM'd before. This is 21 - 9 = 12. Hence, 12/21/9. Keep in mind, the columns we are checking before the Q column in this case are C, E, G, I, K, M, O.

[#13]

@Carl_61 

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.

[#14]

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.

[#15]

@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.

[#16]

@Carl_61 

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.

[#17]

There are 9 PMs within the 21 PMs performed in the month of AUG, column Q that have had prior PMs performed on the same building. So take the 9 away from the 21 and we have 12 buildings of the 21 that had their 1st PM done on them.

[#18]

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.

[#19]

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.

[#20]

@Carl_61 

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))