Assign value based on table

[concenhr23]

Hi,
Hoping someone can help me formulate this. I have a list of products in Column A, e.g. Product number Z3415R has a total of 13. I need to assign a specific amount of products to each of the states I have listed. Right now, I have to assign the state in Column B based on the table I have on the left. So for Product Z3415R, NJ will get three, NY gets two and IL gets 4...etc. For each product number, I have to do the same. I tried IF and COUNTIF <= but I can get to work. Thanks in advance!

					State
Column A	Column B			Product	NJ	NY	IL	OR	TX	Total
Product List	State			Z3415R	3	2	4	1	3	13
Z3415R	NJ			G2891Q	4	3	5	2	4	18
Z3415R	NJ			A7768Y	1	2	2	3	1	9
Z3415R	NJ
Z3415R	NY
Z3415R	NY
Z3415R	IL
Z3415R	IL
Z3415R	IL
Z3415R	IL

 

[Joe4]

Should be a simple COUNTIFS formula (COUNTIFS was introduced with Excel 2010).

If the data in your table is in the range A1:B12 and your first entry in you count table (for "Z3145R/NJ") is in cell F3, enter this formula in cell F3 and copy to the rest of those cells:

=COUNTIFS($A$4:$A$12,$E3,$B$4:$B$12,F$2)

 

[concenhr23]

Should be a simple COUNTIFS formula (COUNTIFS was introduced with Excel 2010).

If the data in your table is in the range A1:B12 and your first entry in you count table (for "Z3145R/NJ") is in cell F3, enter this formula in cell F3 and copy to the rest of those cells:

=COUNTIFS($A$4:$A$12,$E3,$B$4:$B$12,F$2)

Sorry, it's hard to explain. My apologies for the confusion. The table is previously summarized, then I review the list and assign them manually in column D. The product list doesn't have any data in that column to start. I would like to populate Column B based on the table. Hopefully, this makes sense

 

[Joe4]

Sorry, it's hard to explain. My apologies for the confusion. The table is previously summarized, then I review the list and assign them manually in column D. The product list doesn't have any data in that column to start. I would like to populate Column B based on the table. Hopefully, this makes sense

So, are you saying that you are starting off with the table that you have on the right side, and columns A and B are initially blank, and you are trying to build that list in columns A and B form your table?

I think I could figure out a way to do that using VBA, but I do not know of a formulaic approach (without VBA). Not saying that one doesn't exist, it is just beyond my capabilities.

 

[concenhr23]

So, are you saying that you are starting off with the table that you have on the right side, and columns A and B are initially blank, and you are trying to build that list in columns A and B form your table?

I think I could figure out a way to do that using VBA, but I do not know of a formulaic approach (without VBA). Not saying that one doesn't exist, it is just beyond my capabilities.

Column B is blank. The table is a reference on how many products are flagged as the State Code. In other words, NJ gets three product code Z3145R and NY gets two product code Z3145R