Variable Elimination

Shannon Lacerda

I am having trouble trying to eliminate a variable form my formula

=COUNTIFS([Current Status]1:[Current Status]80, NOT(CONTAINS("Early", @cell, "DNP", @cell)))

Kelly Moore

Oh, or do you mean contains GNG? or Completed, as well as the first criteria? Change the word to fit your conditions.

=COUNTIFS([GNG 1 Status]:[GNG 1 Status], CONTAINS("Completed"@cell), [Current Status]:[Current Status], AND(@cell <> "", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell)))))

I apologize for not understanding. See if this is what you meant. If it doesn't work, please tell me what it is not doing

Kelly Moore

Hey @Shannon Lacerda

Try this

=COUNTIFS([Current Status]:[Current Status], AND(@cell<>"", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell))))

cheers

Shannon Lacerda

@KDM thanks,

can you help me with this, I need to same parameter as the last formula to help count column.

=COUNTIFS([GNG 1 Status]1:[GNG 1 Status]80, ([Current Status]:[Current Status], AND(@cell <> "", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell)))))

Kelly Moore

To clarify, we're replacing the [Current Status] column with the [GNG 1 Status] column, but keeping all of the same criteria? Also, I removed the row references in the range- unless you are specifically calling out that range of row1-row80 we can use the whole column reference instead.

=COUNTIFS([GNG 1 Status]:[GNG 1 Status], AND(@cell <> "", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell)))))

If this isn't what you need, let me know what I misunderstood and we can tweak it.

Shannon Lacerda

I need to add up column (GNG Status) but not include the ones that have Early or DNP in the same row.

Kelly Moore

Did the above return the expected values?

Kelly Moore

Oh, or do you mean contains GNG? or Completed, as well as the first criteria? Change the word to fit your conditions.

=COUNTIFS([GNG 1 Status]:[GNG 1 Status], CONTAINS("Completed"@cell), [Current Status]:[Current Status], AND(@cell <> "", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell)))))

I apologize for not understanding. See if this is what you meant. If it doesn't work, please tell me what it is not doing

Shannon Lacerda

@KDM Thanks that worked 👍️

Shannon Lacerda

@KDM so sorry to bother you again,

I have another one, this time I WANT to include the RFP, LOST, WON aspects of the Row, I dont get an error, but I just get a 0. which I know is wrong

=COUNTIFS([GNG 2 Status]:[GNG 2 Status], CONTAINS("Completed", @cell), [Current Status]:[Current Status], AND(@cell <> "", (CONTAINS("RFP", @cell)), (CONTAINS("LOST", @cell)), (CONTAINS("WON", @cell))))

Kelly Moore

Hey @Shannon Lacerda

Sorry I didn't see this post last night, and it's no bother.

Now we need to switch to an OR function.

=COUNTIFS([GNG 2 Status]:[GNG 2 Status], CONTAINS("Completed", @cell), [Current Status]:[Current Status], OR(CONTAINS("RFP", @cell), CONTAINS("LOST", @cell), CONTAINS("WON", @cell)))

Does this return the correct value?

cheers,

Kelly

Shannon Lacerda

@KDM Thank you that worked! :)

Kelly Moore

Always glad to help. Let me know if there's anything else.