Forum Discussion

FHuffman's avatar
FHuffman
Copper Contributor
May 18, 2023
Solved

Formula Troubleshooting

I have an excel sheet that contains entries with both "Due" dates and "Completed" dates.  On a separate sheet, I've created a formula that tallies the number of due dates in a specific month and year...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    May 18, 2023

    FHuffman 

    =SUM((NOT(ISBLANK('Simplified Open and Complet'!J2:J27)))*(YEAR('Simplified Open and Complet'!J2:J27)=A122)*(MONTH('Simplified Open and Complet'!J2:J27)=B122)*(('Simplified Open and Complet'!J2:J27<'Simplified Open and Complet'!K2:K27)+(('Simplified Open and Complet'!J2:J27<TODAY())*(ISBLANK('Simplified Open and Complet'!K2:K27)))))

    An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

FHuffman's avatar
FHuffman
Copper Contributor
May 18, 2023

Hi mtarler; thanks for the quick response!

When using the above formula, I'm getting a count of 0. The resulting number should be 26, some of which have a blank completed date (K column) and others that have a completed date later than the due date (J column). Only 9 have completed dates that are earlier than the due dates.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
May 18, 2023

FHuffman 

=SUM((NOT(ISBLANK('Simplified Open and Complet'!J2:J27)))*(YEAR('Simplified Open and Complet'!J2:J27)=A122)*(MONTH('Simplified Open and Complet'!J2:J27)=B122)*(('Simplified Open and Complet'!J2:J27<'Simplified Open and Complet'!K2:K27)+(('Simplified Open and Complet'!J2:J27<TODAY())*(ISBLANK('Simplified Open and Complet'!K2:K27)))))

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

 

  • FHuffman's avatar
    FHuffman
    Copper Contributor
    May 18, 2023

    Hi OliverScheurich,

     

    Thanks for the response!

     

    This is almost exactly what I need.  The only problem is that if you expand the ranges to any columns where there is no due date, it no longer works (in your example, if you expand J2:J27 to J2:J28, it gives an error).  Is there something that can be added to ensure that it only considers rows in which the due date contains a value?

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      May 18, 2023

      FHuffman 

      =SUM((NOT(ISBLANK('Simplified Open and Complet'!J2:J1000)))*(YEAR('Simplified Open and Complet'!J2:J1000)=A122)*(MONTH('Simplified Open and Complet'!J2:J1000)=B122)*(('Simplified Open and Complet'!J2:J1000<'Simplified Open and Complet'!K2:K1000)+(('Simplified Open and Complet'!J2:J1000<TODAY())*(ISBLANK('Simplified Open and Complet'!K2:K1000)))))

       

      I've expanded the formula and added entries in sheet "Simplified Open and Complet" and the formula returns the intended result.

       

       

      • FHuffman's avatar
        FHuffman
        Copper Contributor
        May 18, 2023

        Hi again OliverScheurich,

         

        Sorry about that!  It was my mistake.  I was referencing the entire column (J:J and K:K) and the headers of the columns were breaking the formula.  I've changed it now to J2:J$1048576 and K2:K$1048576 and it works like a charm.  I've marked your response as the best answer.

         

        Out of curiosity, is there a more elegant way to do J2:J$1048576 (the entire column minus J1)?  I've noticed that J2:J doesn't work as I'd hoped and was unable to find a better solution.

         

        Thanks again!

Resources