Forum Discussion

Carl_61's avatar
Carl_61
Iron Contributor
Nov 23, 2022

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...
Excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Nov 26, 2022

    Carl_61 

    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

Patrick2788's avatar
Patrick2788
Silver Contributor

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

 

Carl_61's avatar
Carl_61
Iron Contributor
Nov 24, 2022
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.