Index match formula not working properly, keeps returning 0

[reddwarf]

Hi,
I am trying to count how many times "Yes" occurs in column D when a match is found on a worksheet called data
This is my formula built up from internet research. It keeps returning 0, but should return 55.

Please Login or Register  to view this content.

Maybe there is a better way to do it?

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[XLent]

the above will return count of Yes in data!An:APn where n is determined by the MATCH
however, note that the MATCH looks as rows $3:$100, not $2:$100 as used by the COUNTIF, so you have an offset being applied
i.e. if MATCH of A2 occurs in data!D3 (row 1 of MATCH range) you are counting Yes' in data!A2:AP2 (row 1 of INDEX range), and not A3:AP3

if the above is not the explanation, (i.e. offset is expected/desired)
- remove the outer IFERROR to see if the 0 is being returned because of an underlying error (i.e. the MATCH) rather than the COUNT itself being 0
- if non-error and count is 0, check the underlying values on Data actually contain "Yes" (and no other characters etc)

[6StringJazzer]

I can think of a couple of ways to interpret your description, and I can't reconcile your description with the formula you are giving us.

Can you attach the file so we can see your data?

[reddwarf]

Thanks for your help.
Mock up attached (I had to remove sensitive data)

Book1.xlsx

[AliGW]

There is no match. What are you expecting it to return? There's an #NA error that is returning the 0, which is what you've told it to do.

Explain in detail what the objective is.

[windknife]

B2
=IFERROR(COUNTIF(INDEX(data!$A$3:$J$100,,MATCH($A2,data!$A$2:$J$2, 0) ), B$1), 0)

copied down and across.

[reddwarf]

Thank you. This works perfectly. Really appreciated

[windknife]

You are welcome.