COUNTIFS HELP!

Kyle Jarrett

Hello,

I'm attempting to write a formula that says count if this branch is rendered but not if this column shows "PTB"... I got the first part to work and it counts if the branch is in there but cannot get it to not count those with PTB in the PTB? column. if you need any further explanation please let me know!

Logic:

Count if Branch is "Schererville Stracks 101" but not if PTB? column shows "PTB"

=COUNTIFS([Branch #]:[Branch #], "Schererville Stracks 101", [PTB?]:[PTB?], NOT(FIND("PTB", LOWER(@cell)) = "PTB"))

Paul Newcome

Part of the problem is that you are searching for capitalized text within lower case data. There is also an issue with that criteria set in that FIND generates a numeric value, but you are saying "equals text". Here is a different way to write that criteria that may be a little more simplified. Give it a shot and see how it works.

=COUNTIFS([Branch #]:[Branch #], "Schererville Stracks 101", [PTB?]:[PTB?], <> "PTB")

Kyle Jarrett

@Paul Newcome Thanks for the idea. I did actually try it that way and it wasn't working. For some reason this won't even count when the branch column is the Schererville Stracks 101 value. That's the only reason I went the more complex route. I wasn't even paying attention on the lower case values so thank you for that!

Really dumb question because I know it looks for values but i'll ask anyway.. is it because I am pulling the information through a data mesh/this isn't a dropdown column?

Any other ideas?

Kyle Jarrett

@Paul Newcome it shouldn't matter but I figured I'd mention this is in the sheet summary where I am attempting to write this.

Paul Newcome

Writing this in a Sheet Summary field should not make a difference.

Double check that your spelling is correct. I notice there is a space after 101 in your formula. If there is no space in the cells afterwards, then it won't count. Try removing that and see if it works.

Kyle Jarrett

@Paul Newcome still no such luck unfortunately. Idk why that one is not working because in theory that is what we are saying. oddly confused why this doesn't work.

Andrée Starå

Hi @Kyle Jarrett

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Kyle Jarrett

Hi @Andrée Starå ,

I was getting some help from my smartsheet customer success rep. and when I went in to see what she had done this morning I changed a few things in the formula to what I thought it should be and it is working now. Idk why it decided to work now but I got it to work using the below formula with multiple successes.

=COUNTIFS([Branch #]:[Branch #], "Southlake 036", [PTB?]:[PTB?], <>"PTB")

Andrée Starå

@Kyle Jarrett Ok. Glad you got it working!