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

Thank you @Matt LynnPCG. 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)

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

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

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

@Matt LynnPCG Thank you for all your help!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 349 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!