Forum Discussion

LisaSingleton's avatar
LisaSingleton
Copper Contributor
Jun 09, 2025
Solved

Help with Sum function

How can I add another variable to the below formula?  I would like to sum all defects that are not closed and deferred.  

=SUM(N(TOCOL('All Defects'!I:I,1)<>"Closed"))

 

Thanks

  • =SUM((TOCOL('All Defects'!I:I,1)<>"Closed")*(TOCOL('All Defects'!I:I,1)<>"Deferred"))

    which can be shortened to

    =LET(c, TOCOL('All Defects'!I:I,1), SUM((c<>"Closed")*(c<>"Deferred")))

    or a COUNTIFS formula:

    =COUNTIFS('All Defects'!I:I, "<>", 'All Defects'!I:I, "<>Closed", 'All Defects'!I:I, "<>Deferred")

2 Replies

  • =SUM((TOCOL('All Defects'!I:I,1)<>"Closed")*(TOCOL('All Defects'!I:I,1)<>"Deferred"))

    which can be shortened to

    =LET(c, TOCOL('All Defects'!I:I,1), SUM((c<>"Closed")*(c<>"Deferred")))

    or a COUNTIFS formula:

    =COUNTIFS('All Defects'!I:I, "<>", 'All Defects'!I:I, "<>Closed", 'All Defects'!I:I, "<>Deferred")

Resources