COUNTIF in an IF formula

Jade Boring

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?

Davin Vo

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

Matt Lynn-PCG

@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)

Jade Boring

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)

Matt Lynn-PCG

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

Matt Lynn-PCG

@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)

Jade Boring

@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.

Matt Lynn-PCG

@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.

https://app.smartsheet.com/b/publish?EQBCT=ecbaa7ae5e8d48ca9d34aca62d04f959

https://scheduler.zoom.us/matt-lynn/15-min-help-sync

Jade Boring

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