Sheet Summary Formula using cell populated by Index/Match  #NO MATCH
Attempting to use Sheet Summary to total all "Lab" Products shipped in "202208."
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, "202208", 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, "202208", 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

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

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.

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

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
Categories
Check out the Formula Handbook template!