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