Count if formula with multiple criteria where a field contains certain text

latheobald89201

Hello,

I am having trouble writing a formula to count rows. Where row 1 = Yes where row2 does not contain "virtual" or "needed" (these single words are fragments of the selection in the drop-down list). This formula is on one sheet and counting values from a different sheet.

I can get this to work in reverse ie count row 1 if yes and count row 2 where row2 contains "Virtual" or "Needed" but I can't get it to work with the Not.

I have tried it both of the ways below.

=COUNTIFS({Summer Program}, "Yes", {On site desk 1}, OR(CONTAINS(NOT("Virtual", @cell), CONTAINS(NOT("needed", @cell)))))

=COUNTIFS({Summer Program}, "Yes", {On site desk 1}, OR(NOT(CONTAINS("Virtual", @cell), CONTAINS("needed", @cell))))

Lucas Rayala

Hi @latheobald89201, I'm pretty sure if you try to put two "Not" statements together in an "Or", you will select everything (if you are pointing this at a single-select pulldown).

Try breaking them up:

=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)))

latheobald89201

@Lucas Rayala Thank you that is close it is also counting blanks. I tried adding NOT(ISBLANK but I just can't get the syntax right

=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)),{On site desk 1}, NOT(ISBLANK, (@cell)))

Lucas Rayala

Hi @latheobald89201, you need to exclude the extra coma in front of the "ISBLANK" term in your formula.

=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)),{On site desk 1}, NOT(ISBLANK(@cell)))