count how many text strings in a range match full list of text strings and multiply by another data column

lukaszzyla

New Member
Joined
Nov 9, 2023
Messages
2
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
Sounds mysterious but maybe pictures will help:
I have a range of text strings (list of products) - 100 - 500 different names:
1699515968356.png


this is a list of products that we need to manufacture. Each one of them has unique name (column A: B.13 B.14 etc) , we need different quantity of each of those (column B) and each one of them has different area (column D) we need a way to track down number of pieces and calculate area of every type manufactured (there might be more than one piece needed)
Then, we are manfactuing few of them every day and we manually write down their names:
1699516087532.png


Because it lasts months usually and we need to know exactly how many of which we do have, and how many more are left for manufacturing, and how many have me manufactured in terms of pieces and area.
Currently we use crazy long functions that excel doesnt accept sometimes because formula exceeds 8100 letters:
to get number of pieces manufactured every day we use:
=COUNTIFS(L7:L49,list!A2:A261) from manually enetered manufactured product references (each column above is different produciton day
and to get area manufactured we use another function:
=COUNTIF(L7:L50,"N1.1")*VLOOKUP("N1.1",list!$A$2:$W$300,3,0)

but they get crazily long if the list form first picture has a lot of unique names/types:
1699516585917.png


(this is just a part)

What I would like to achieve is:
check daily manufacturing list AGAINST full list of types and calculate area of daily manufactured products every day.
Anyone that has an idea how to deal with it?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
mistake in first formula that we use. COUNTIF not COUTIFS:
=COUNTIF(L7:L49,list!A2:A261)
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top