Index Matching + ranged CountA

Hi guys, thanks in advance.

As always, attached is an example for reference and I will walk through what I am trying to do using the attached example.

Issue: What formula would I use to do index matching to look up and count the number of products in a range? (note, it will be index matching in another workbook, so it needs to reference the other workbook).
Here's my example:

I need a formula to reference the code in column A to look up the code in column D (the black bar is meant to visually represent that columns d-s are a different workbook) and count the number of x's (products) in range E:S. The number of products should be returned in column B (where the ? is). Unfortunately it wont actually be that easy because its going to be something like count the number of x's (products) in columns E:I and O:Y, meaning there is a break of 5 columns it would actually be counting (not represented in this example). I'm not sure if that makes a difference for the formula.

Also, as you can see, the codes in column D are in no particular order. Preferably I would like to leave the data the way it is arranged and not have to sequentially order the codes. There are a total of 70 codes, but I would like the formula to reference 100 rows total, so rows 2:102. After code 70, no more codes are input. I just added a little buffer into the range.

In this example, cell B2 should return a value of 4, since there are four x's in E15:S15 (note the code in column D).

Thank you so much!!!


Excel Forum Help.xlsx

No workbook attached. We can't work with a picture of one - we need the real thing.

Thanks for that tidbit Ali! My apologies, I've always posted a picture before.

As in the picture, I'm trying to create a code that references the code in column A (sheet 1), looks up the same code in column c (sheet 2), counts the number of products (x's D:R, sheet 2), and then returns the correct value in column B (sheet 1). As an example, cell B11 sheet 1 should return a value of 3. Workbook is attached in the original post.

Excel Forum Help.xlsx

In B2 copied down:

=COUNTA(INDEX(Sheet2!$D$2:$R$101,MATCH(A2,Sheet2!$C$2:$C$101,0),0))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.