Sheet Summary Formula using cell populated by Index/Match - #NO MATCH

Attempting to use Sheet Summary to total all "Lab" Products shipped in "2022-08."

The Product Type (Lab) in the Product column is pulled in using Index/Match. When I enter the formula below, using info pulled in by Index/Match, I receive the #NO MATCH" error.

=COUNTIFS(SHIP:SHIP, "2022-08", Product:Product, "Lab") - #NO MATCH

I added a column titled "Test" and entered Product types manually, and formula calculates a value, no errors.

=COUNTIFS(SHIP:SHIP, "2022-08", Test:Test, "Lab") = 2

I looked at examples and believe I did see where cross referenced cells were used in a Sheet Summary formula, but guessing formula will not work with value pulled in by Index/Match or VLookup?

Any response is appreciated.

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    That should not be the case. Can you share the formula and sheet screenshot, if possible?

  • Smarty
    Smarty ✭✭✭








    Above are the columns I am working with. The "Test" column data mirrors the "Product" column data, except in the "Test" column I am entering the data. Sheet summary attempts below show using the Ship and Test columns returns a value, while using Ship and Product column does not.


  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭

    Hi @Smarty

    Had something similar happen and it appeared the COUNTIFS was reading the formula not the result, can you add a hidden helper column that simply has the formula =Product@row (convert to a column formula) and refer to that range instead of the product range in the formula and see if that fixes it?

    Hope that helps

    Thanks

    Paul

  • Smarty
    Smarty ✭✭✭

    Paul, would you believe, same issue when I use a helper column! I know when I am beaten, going to come up with a new plan and new solution. Thanks for the suggestion.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!