count how many of which

dado6612

Well-known Member
Joined
Dec 4, 2009
Messages
591
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 :biggrin:

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:rolleyes:
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 

dado6612

Well-known Member
Joined
Dec 4, 2009
Messages
591
Excel Workbook
ABCDEFGHI
1Choose factory
2Fruit
3FruitUpBDay 1Day 2Day 3TotalBaskets
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:

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

dado6612

Well-known Member
Joined
Dec 4, 2009
Messages
591

ADVERTISEMENT

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 :)
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

dado6612

Well-known Member
Joined
Dec 4, 2009
Messages
591

ADVERTISEMENT

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
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,175
Members
417,259
Latest member
gtacw

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
Top