# count how many of which

##### Well-known Member
Hi to all

In range A4:A7 i have list of products
I have other table with names of factories where i type how many units of some product that factory made

Every factory make always the same product from the first list

Arghh its hard to explain

there are 30 factories..

number of factories are not always the same in same position

After i choose which product of 4 in list some factory made i type number of units done that day for each factory

what i need is count how many units of each is made that day

ex. apples, oranges, bananas and peach

some factory make something of those fruits and if factory1 made 10 apples, factory2 14 oranges, factory3 20 oranges, factory4 44 bananas and so on..

i need at the and this
apples: 100
oranges: 47
bananas: 200
peach: 32

what every factory make is in range B15:DN15 (every second column, and they are merged, so actually its every 4th)

in range B17:B118 is number of units made each day for selected factory
for second factory that list is in range F17:F118 and for next factory is 4 column ahead and so on..

I don't know how to describe it better and i hope u experts understand what i looking for

and i also hope ill get answer which will solve mine problem

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Install and use Excel Jeanie to post up some visible examples of the data you're referring to, and perhaps a mockup of the desired results you have in mind.

Excel Workbook
ABCDEFGHI
1Choose factory
2Fruit
4Apple202326711206
5Orange152573271258,33333
6Bananas403246681463,65
7Peach15252312116911,2667
8FruitUpBFruitUpBFruitUpBFruitUpB
9Orange15Bananas40Peach15Apple20
10OrangeBananasPeachApple
11125322523
12273462326
133276812171
Sheet1
Cell Formulas
RangeFormula
F4=SUM(C4:E4)
F5=SUM(C5:E5)
F6=SUM(C6:E6)
F7=SUM(C7:E7)
F9=F10
G4=F4/B4
G5=F5/B5
G6=F6/B6
G7=F7/B7
G9=VLOOKUP(F9,\$A\$4:\$B\$7,2,0)
B9=B10
C9=VLOOKUP(B9,\$A\$4:\$B\$7,2,0)
D9=D10
E9=VLOOKUP(D9,\$A\$4:\$B\$7,2,0)
H9=H10
I9=VLOOKUP(H9,\$A\$4:\$B\$7,2,0)

yellow table is what i want, this is only shorted example, as i said there are 30 companies more which looks like this shorted example

Last edited:
Do you mean like:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 120px"><COL style="WIDTH: 54px"><COL style="WIDTH: 45px"><COL style="WIDTH: 62px"><COL style="WIDTH: 45px"><COL style="WIDTH: 47px"><COL style="WIDTH: 60px"><COL style="WIDTH: 43px"><COL style="WIDTH: 32px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Choose factory</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Fruit</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff">Fruit</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff">UpB</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">Day 1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">Day 2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">Day 3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Total</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Baskets</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Apple</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">23</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">26</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">71</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">120</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">6</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Orange</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">73</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">27</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">125</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">833,333</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Bananas</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">40</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">32</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">46</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">68</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">146</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">3,65</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Peach</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">23</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">121</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">169</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">112,667</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">Fruit</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">UpB</TD><TD style="FONT-FAMILY: Verdana">Fruit</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">UpB</TD><TD style="FONT-FAMILY: Verdana">Fruit</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">UpB</TD><TD style="FONT-FAMILY: Verdana">Fruit</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">UpB</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">Orange</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">15</TD><TD style="FONT-FAMILY: Verdana">Bananas</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">40</TD><TD style="FONT-FAMILY: Verdana">Peach</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">15</TD><TD style="FONT-FAMILY: Verdana">Apple</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">20</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Orange</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Bananas</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Peach</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">Apple</TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">25</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">32</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">25</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">23</TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">73</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">46</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">23</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">26</TD><TD style="FONT-FAMILY: Verdana"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">27</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">68</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">121</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">71</TD><TD style="FONT-FAMILY: Verdana"></TD></TR></TBODY></TABLE>
Formula in C4:

Code:
``=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(C\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A4,\$B\$10:\$I\$10,0))``

Which can then be copied down and across...

Matty

No, cause factory ca be changed..
so in place 1, where now is Orange can be Apple, and then formula should sum all unit of same apples factory for that day
see how it should look like after factory type change
Excel Workbook
CDE
3Day 1Day 2Day 3
4489998
5000
6324668
72523121
Sheet1
Cell Formulas
RangeFormula
C4=B11+H11
C6=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(C\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A6,\$B\$10:\$I\$10,0))
C7=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(C\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A7,\$B\$10:\$I\$10,0))
D4=B12+H12
D6=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(D\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A6,\$B\$10:\$I\$10,0))
D7=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(D\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A7,\$B\$10:\$I\$10,0))
E4=B13+H13
E6=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(E\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A6,\$B\$10:\$I\$10,0))
E7=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(E\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A7,\$B\$10:\$I\$10,0))

But your formula is quite interesting for some other cases

Sorry, but I am struggling to understand what you mean.

Could you share how your data is now laid out given the factory change?

Could you also explain exactly how the result you're after is arrived at?

Matty

Excel Workbook
CDE
3Day 1Day 2Day 3
4257327
5#N/A#N/A#N/A
6324668
72523121
Sheet1
Cell Formulas
RangeFormula
C4=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(C\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A4,\$B\$10:\$I\$10,0))
C5=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(C\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A5,\$B\$10:\$I\$10,0))
C6=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(C\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A6,\$B\$10:\$I\$10,0))
C7=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(C\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A7,\$B\$10:\$I\$10,0))
D4=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(D\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A4,\$B\$10:\$I\$10,0))
D5=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(D\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A5,\$B\$10:\$I\$10,0))
D6=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(D\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A6,\$B\$10:\$I\$10,0))
D7=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(D\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A7,\$B\$10:\$I\$10,0))
E4=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(E\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A4,\$B\$10:\$I\$10,0))
E5=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(E\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A5,\$B\$10:\$I\$10,0))
E6=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(E\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A6,\$B\$10:\$I\$10,0))
E7=INDEX(\$B\$11:\$I\$13,MATCH(VALUE(TRIM(SUBSTITUTE(E\$3,"Day",""))),\$A\$11:\$A\$13,0),MATCH(\$A7,\$B\$10:\$I\$10,0))

this is what i get when i change Orange factory into Apple
what i posted in previous post is what i should get

OK. Given the set up I posted previously, try...

Code:
``=SUM(IF(\$A\$11:\$A\$13=VALUE(TRIM(SUBSTITUTE(C\$3,"Day",""))),IF(\$B\$10:\$I\$10=\$A4,\$B\$11:\$I\$13)))``

in C4, which needs committing with Ctrl+Shift+Enter.

Matty

Perfect dude

thanks ray:

thanks ray:

You're welcome. And cheers - I am just enjoying a cold one too!

Matty

Replies
1
Views
190
Replies
2
Views
305
Replies
3
Views
226
Replies
1
Views
214
Replies
21
Views
1K

1,211,772
Messages
6,103,876
Members
447,882
Latest member
LORENA

### 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.

### Which adblocker are you using?

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

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