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.

     

mtarler's avatar
mtarler
Silver Contributor
May 18, 2023
I would recommend if you have COUNTIFS to use that:
=COUNTIFS('Simplified Open and Complet'!J:J, "<>",
'Simplified Open and Complet'!J:J, "<"&TODAY(),
'Simplified Open and Complet'!J:J, "<" & 'Simplified Open and Complet'!K:K,
'Simplified Open and Complet'!J:J, ">=" & DATE(A122, B122,1),
'Simplified Open and Complet'!J:J, "<" & EDATE(DATE(A122, B122,1),1) )
  • 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?

Resources