count how many of which

D

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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
count how many text strings in a range match full list of text strings and multiply by another data column
Replies
1
Views
211
lukaszzyla
L
Z
  • Question
Creating a report/list from a count
Replies
0
Views
62
Zeke247
Z
M
  • Solved
Sum of numbers associated with text
Replies
2
Views
315
MistyWind
M
N
  • Solved
Find how many times text in a specific range is found in another specific range
Replies
3
Views
255
mumps
mumps
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
404
KRice
K

Forum statistics

Threads
1,214,590
Messages
6,120,421
Members
448,961
Latest member
nzskater

Share this page

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