COUNTIF and VLOOKUP?

Good afternoon everyone,

I hit a bit of a snag and I am curious to know if I may get some help. 

I have a tracker that we are using Smartsheet for and based on the type of item that is added in, only certain check boxes are required to complete the work. 

Originally I had done a massive COUNTIF formula where I had typed up each one of the types and basically said that if the checkbox is true and the type is not x, y, and z, then count it. This initially worked but I was hoping to make it a little easier in case someone else needs to make changes in the document as requirements may change in the future.

I thought about creating a support table to make it a little easier and added columns that mirror the source table but note whether or not something was required. I hoped that I could combine a VLOOKUP with the COUNTIF but it is not counting the checkbox. This is the current formula I am trying:

=COUNTIFS([First Approval]@row, True, (VLOOKUP(Type@row, {Support Table}, 2), "Required")

If worst comes to worst, I can create another set of support columns to pull the VLOOKUP and then do a COUNTIFS(Column1, True, Column2, "Required") but that'll essentially double all the columns we have already so it isn't my first preference.

Anyone have any thoughts?

Tags:

Best Answers

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    at a glance this seems to need a count(collect()) but i'm not 100% sure. Can you post some snips of your sheets with any confidential information blacked out?

  • Sure thing, here's a few screenshots.

    The first is the support table:

    And here is the main table:

    The Support column is where I have the formulas. Right now they are using the older formula:

    =COUNTIFS([First Approver]@row, 1, Type@row, <>"Water", Type@row, <>"Waste", Type@row, <>"Power & Water", Type@row, <>"Water & Waste", Type@row, <>"FLS System", Type@row, <>"Sprinker System", Type@row, <>"Non-Impactful Power", Type@row, <>"Single Amenity Water", Type@row, <>"Single Amenity Waste", Type@row, <>"Floor Grinding", Type@row, <>"Impactful X-Ray", Type@row, <>"Non-Impactful X-Ray", Type@row, <>"Building Closure")

    Which works fine as it is - but could be an issue down the line if requirements change for different types, especially when dealing with so many columns.

  • Ahh that helps out! I was able to get it to acknowledge whether or not the corresponding column in the support table is confirmed.

    Sorry, I had taken a screenshot from my working copy instead of the live copy is it may have been a bit confusing.

    How would I add in the checkbox so that it counts both requirements as 1?

    Here's the formula I tried tweaking based on your formula:

    =IF(AND(INDEX({Support Table: First Approver}, MATCH(Type@row, {Support Table: Type}, 0)) = "Confirm"), [First Approver]@row = true) 1, 0)

    Seemed pretty straight forward to me but I ended up with an error.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!