Forum Discussion
LisaSingleton
Jun 09, 2025Copper Contributor
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
Sort By
=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")
- LisaSingletonCopper Contributor
This worked! Thanks so much.