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

Smarty

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.

Sameer Karkhanis

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

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

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

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.