Forum Discussion
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")
=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")
This worked! Thanks so much.