Excel formula help

Question

I am needing help with a formula hopefully this is the right place to post this. I have spent a lot of time trying to figure this out watching videos and searching the Web so hopefully someone can help or point me in the right direction.

I have multiple sheets and in the cells it will have a mixture of numbers and they will be different.

example

2x4 16<br><br><br>5.27 4p	2x4 16<br><br><br>5.27 1p

2x6 12<br><br><br>5.27 4p	2x6 12<br><br><br>5.27 4p

I need a formula that can identify whether it's 2x4 or 2x6 then I need it to add the number in front of the p. 

Example 

2x4 16 = 5p

2x6 12 = 8p

Answer 1

Refer below image, use the formula in M2 cell and copy down:

=SUMIF($B$2:$B$13,$L2,$C$2:$C$13)+SUMIF($D$2:$D$13,$L2,$E$2:$E$13)+SUMIF($F$2:$F$13,$L2,$G$2:$G$13)+SUMIF($H$2:$H$13,$L2,$I$2:$I$13)

One thing, in H4 cell and H6 cell not appeared the complete product name like so the sum not returned properly for "4x6 16". This should be 23 but as per data it returned 15.

Answer 2

Each column will have the product example 2x4 16, 4x4 16, etc.. the cell below will be the date, the cell to the right will have how many packs are in the group... so in cell 1-1 underlined in below picture, you have 2x4 16, date 5/30, and 4 packs. what i am needing and i was trying to achieve with sumifs. I am needing it to add all the packs for each criterion.

so the answer should be 2x4 16s = 31

4x4 16s =18

4x6 16s = 23

The blank cells need to be in the formula too due to they at some point will all have data in them with different products. 

	1	2	3	4	5	6	7	8	9
1	2x4 16	4	4x4 16	4	2x4 16	4	4x6 16	4
5/30	5/20	5/15	5/30
2	2x4 16	4	4x4 16	4	2x4 16	4	4x6	4
5/30	5/21	5/15	5/30
3	2x4 16	4	4x4 16	4	2x4 16	1	4x6	4
5/30	5/21	5/16	5/28
4	2x4 16	4	4x4 16	4			4x6 16	4
5/29	5/22		5/28
5	2x4 16	4	4x4 16	2			4x6 16	4
5/29	4x4 16		5/27
6	2x4 16	2					4x6 16	3
5/28			5/26
7
8
9
10

So below is where the number of packs should be added so i am trying to find a formula that will look at all the cells if 2x4 16 then add the number of packs in the 2x4 16.

2x4
16
14
12
10
8

Answer 3

I did not understand why 8p and 5p placed based on your given data. Where can I find how many packs are in the row? if you shared some sample data with exact cell location and expected value in exact cell location, then it would be easier to help you.

Please share some snapshots or share your sample file.

Answer 4

Thank you for your response. That is not working the way i need it to. i didnt explain it well..

I am trying to create basically an inventory tracker without having to use barcodes and scanners. I have tried several progams out there for inventory management and none so far has what i am wanting.. im not against buying one i just havent found one..

Its a lumber yard the products are 2x4, 2x6, 4x4, 4x6 then we have length of each type  16 ft, 14ft, 12ft, 10ft, 8ft.  

I have set up excel with the diagram of the yard, where as the lumber packs are being made we can mark where they go and how many packs are in the row. i am needing a formula that can go thru the entire sheet and add all the packs of each group. 

So in the cells i will have 2x4 16 1p or 2p  3p etc. The number infront of p represents how many packs. So depending how many packs have been placed in that row. The other rows will be a mixture of the different sizes length and packs..

So i am needing the total number of packs to add up for each size and length..

Answer 5

Hi, with the reference of below image:

Use the formula in D1 cell and copy down:

=CONCATENATE(LEFT($A1,6)," = ",IF(LEFT(A1,3)="2x4","5p","8p"))