COUNTIF and VLOOKUP?

Ryan Nix

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?

L_123

I think i'm missing something. All of the items on the second snip are confirmed in the first snip, so why is power the only one that is different?

If you want to check if the corresponding value in the helper table is "confirmed", then this formula will do it.

=if(index([First Approver]:[First Approver],match(type:type,type@row,0))="Confirmed", 1,0)

L_123

sorry didn't realize you asked another question.

Your formula is missing a comma before the one.

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

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?

Ryan Nix

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.

L_123

I think i'm missing something. All of the items on the second snip are confirmed in the first snip, so why is power the only one that is different?

If you want to check if the corresponding value in the helper table is "confirmed", then this formula will do it.

=if(index([First Approver]:[First Approver],match(type:type,type@row,0))="Confirmed", 1,0)

Ryan Nix

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.

L_123

sorry didn't realize you asked another question.

Your formula is missing a comma before the one.

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