INDEX MATCH COUNT

[BarakUSA.com]

Hello all,

First, thank you to anyone that attempts to help.
Second, I hope to keep the explanation as simple as possible.

I have a table of values, A:O, on one sheet that is several thousand rows long.
I am using index & match to populate form a data validation list based off of a selection from A on a different sheet.
I need a formula that reads the data validation from A and counts filled cells in the matching row in cells J:O.

Thus, for example:
If 'Sheet 2'!A matches a cell value in 'Sheet 1'column A then count matching row in 'Sheet 1'column A cells J:O

When I put it like that it seems easy but I cannot figure it out.

Thank you again,
Mark

 

[Somendra Misra]

Hi Mark,

Welcome to the forum.

It would be helpful for you and others if you can illustrate what you are describing on a sample file. Kindly, upload one.

Regards,

 

[BarakUSA.com]

File attached.
Again, sheet 1 has values in a table.
Sheet 2 extracts values based upon $A$1 data validation selection.
For "Color quantity" (A7/B7) I need a formula that matches the $A$1 data validation selection to sheet 1 and counta in the matching row on sheet 1 columns J:O.

Thus, the formula should return values as follows:
A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 6

Thank you again,
Also thanks for the quick reply.

 

[Somendra Misra]

@BarakUSA.com

Shouldn't for all A's it should be 6 as per your data.

Regards,

 

[BarakUSA.com]

You're correct, I didn't save the file before I uploaded it, thus it had 6 for all cells. Apologies.
You'll notice that if I select A5, I get F5 for job name. I would also like a value returned for color quantity that counta 6.

 

[Somendra Misra]

Try in B7 on Sheet2:

=SUMPRODUCT(--NOT(ISBLANK(INDEX('Sheet 1'!$J$2:$O$6,MATCH('Sheet 2'!$A$1,'Sheet 1'!$A$2:$A$6,0),))))

Regards,

 

[Haseeb A]

Hello Mark,

You could also COUNTA with INDEX,

=COUNTA(INDEX('Sheet 1'!J:O,MATCH(A$1,'Sheet 1'!A:A,0),0))

Note: Your VLOOKUP is looking for approximate match not for exact. For accurate result you must use 0 after col_index_num in VLOOKUP.

 

[BarakUSA.com]

Thank you both, I will try these later today. I didn't think to try putting the counta before the index/match and that is most likely why I had problems.

Much appreciated!