COUNTIF in an IF formula

✭✭✭✭

I am trying to see when a segment is fully completed. Our segments can be broken out into subsegments, and they can have up to 5 subsegments, indicated by the segment number followed by a "—1", "—2", and so on. So far for this formula I've gotten it to count how many subsegments there are, but can't get the final push of it to tell me if all of those subsegments are completed (if the check box is checked).

=IF(COUNTIF(Segment:Segment, =Segment@row) = COUNTIF(Segment:Segment, =Segment@row, [Subsegment Complete]:[Subsegment Complete], 1), 1, 0)

This formula is giving an incorrect argument error. Any ideas?

Tags:

Answers

• ✭✭✭

Hello @Jade Boring at a quick glance, you have a couple of extra equal signs in there, and with the 2nd COUNTIF statement you may need to use a COUNTIFS since you have multiple criteria.

Try this!

=IF(COUNTIF(Segment:Segment, Segment@row) = COUNTIFS(Segment:Segment, Segment@row, [Subsegment Complete]:[Subsegment Complete], 1), 1, 0)

Hope that helps

Davin Vo - Sevan Technology

Smartsheet Platinum Partner

• ✭✭✭✭✭✭

@Jade Boring Try this… works for me :

=IF(COUNTIF(Segment:Segment, Value@row = Segment@row) = COUNTIFS(Segment:Segment, Value@row = Segment@row, [Subsegment Complete]:[Subsegment Complete], 1), 1, 0)

Certified Platinum Partner

2023 Partner of the Year

PrimeConsulting.com

• ✭✭✭✭
edited 08/27/24

Thank you @Matt Lynn-PCG. The Value@row doesn't seem to be working for me for some reason.

This is the part of the formula that is giving me the invalid value error "[Subsegment Complete]:[Subsegment Complete], 1". I've tried to put the whole COUNTIFS in a VALUE() and VALUE([Subsegment Complete]:[Subsegment Complete]); however, those aren't working either.

=IF(VALUE(COUNTIF(Segment:Segment, Segment@row)) = VALUE(COUNTIFS(Segment:Segment, Segment@row, [Subsegment Complete]:[Subsegment Complete], 1)), 1, 0)

=IF(VALUE(COUNTIF(Segment:Segment, Segment@row) = COUNTIFS(Segment:Segment, Segment@row, [Subsegment Complete]:[Subsegment Complete], 1)), 1, 0)

• ✭✭✭✭✭✭
edited 08/27/24

…. see the other comment below. I think I found my silly mistake.

Certified Platinum Partner

2023 Partner of the Year

PrimeConsulting.com

• ✭✭✭✭✭✭

@Jade Boring Try this one instead, Sorry…:

=IF(COUNTIF(Segment:Segment, @cell = Segment@row) = COUNTIFS(Segment:Segment, @cell = Segment@row, [Subsegment Complete]:[Subsegment Complete], 1), 1, 0)

Certified Platinum Partner

2023 Partner of the Year

PrimeConsulting.com

• ✭✭✭✭

@Matt Lynn-PCG I appreciate your help with this! However, it is still not working. The highlighted part of this formula is giving the value error. It won't work on its own, when the other parts of the formula are working fine on their own.

• ✭✭✭✭✭✭

@Jade Boring let's try to look at it together. It's working on my end so I'm not sure how to help without seeing yours directly. The below is a schedule link for a quick sync if you're interested. Or here's a link to the published version that is working. Note the highlighted portion isn't a full formula and wouldn't work anyway… but maybe that's not what you meant.

Certified Platinum Partner

2023 Partner of the Year

PrimeConsulting.com

• ✭✭✭✭

@Matt Lynn-PCG Thank you for all your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!