Count Numbers Next To Variables

[rvill189]

Hello,

I am trying to create a formula in a daily inventory that calculates the number of pallets (indicated as P) in a column. As you can see below screenshot examples, "<value>P" is not always in the same position in the cell, and cells contain values that I do not need to account for (boxes, rolls, the "+" and "=" signs, etc.):

I want the formula to evaluate column E:E and calculate the number next to P only and whenever it exists next to P.

As an example between the three screenshots, the total the formula should return is 159.

 

[kweaver]

Welcome to Mr. Excel,

It would help if you show your data and expected results using XL2BB so that it's understood (at least for me) what the solutions are.

 

[rvill189]

Welcome to Mr. Excel,

It would help if you show your data and expected results using XL2BB so that it's understood (at least for me) what the solutions are.

Thank you,

Pallet Inventory.xlsx
	A	B	C	D
9	LABEL Quantity	PHYSICAL Quantity	RECEIVE DATE	DAYS IN WAREHOUSE
10	1	1P
11	1	1P
12	11	11P
13	1	1P
14	6	5BXS+1P
15	40	40P
16	8	8P
17	18	18P
18	NO QTY	9P
19	14	14P
20	7	7P
21	7	7P
22	5	5P
23	2	2P
24	7	7P
25	8	8P
26	10	10P
27	40	5P+35BXS
28	13	13P
29	4	4P
30	5	2P+3BXS
Product Price List

Cell Formulas
Range	Formula
D10:D30	D10	=IF(ISBLANK([@[RECEIVE DATE]]),"",ABS(DAYS([@[RECEIVE DATE]],TODAY())))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C106:D1048576,C5,C8:C9	Cell Value	contains "completo"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A9:B9	Any value
C9	Any value
D9:D30	Any value

 

[rvill189]

Reduced to what I am only looking at:

Pallet Inventory.xlsx
	A	B	C	D
9	PHYSICAL Quantity		Pallet total:
10	1P
11	1P
12	11P
13	1P
14	5BXS+1P
15	40P
16	8P
17	18P
18	9P
19	14P
20	7P
21	7P
22	5P
23	2P
24	7P
25	8P
26	10P
27	5P+35BXS
28	13P
29	4P
30	2P+3BXS
Product Price List

Cells with Data Validation
Cell	Allow	Criteria
A9	Any value

 

[kweaver]

What results do you want that are associated with the rows?
Would be helpful if you changed your profile to indicate what version of Excel you run.

 

[rvill189]

What results do you want that are associated with the rows?

I need a formula that evaluates the column and calculates the values attached to P only. P represents "pallets on inventory". In the second table I attached, the formula should evaluate column A (or Physical Quantity) and return 174. The formula would ignore everything else that does not have a value attached to P.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would employ a user-defined function for this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below, adjusting for whatever your actual table name is.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Pallets(r As Range) As Long
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(.+?)(\d+P)"
    Pallets = Evaluate(Replace(.Replace(" " & Join(Application.Transpose(r)) & " 0P", "+$2"), "P", ""))
  End With
End Function

rvill189.xlsm
	A	B	C	D	E	F
1	LABEL Quantity	PHYSICAL Quantity			Pallet total:	159
2		8P
3		1P
4		23BXS=2P
5		2P
6		1P+33BXS
7		44P
8		40P
9		2P
10		11P
11		10P
12		5BXS
13		9P=32ROLLS
14		10P
15		1BX
16		6BXS
17		2P+17BXS
18		5P
19		10P
20		2P+17BXS
21
22
Sheet1

Cell Formulas
Range	Formula
F1	F1	=Pallets(Table2[PHYSICAL Quantity])

 

[jtakw]

Hi,

Here's one way with a Helper column in B (or anywhere on your sheet) that you can hide, all the "heavy lifting" is done in the helper:

Book3.xlsx
	A	B	C	D
1	PHYSICAL Quantity		Pallet total:	183
2	1P	1P
3	1P	1P
4	11P	11P
5	1P	1P
6	5BXS+1P	1P
7	40P	40P
8	8P	8P
9	18P	18P
10	9P	9P
11	14P	14P
12	7P	7P
13	7P	7P
14	5P	5P
15	2P	2P
16	7P	7P
17	8P	8P
18	10P	10P
19	5P+35BXS	5P
20	13P	13P
21	4P	4P
22	2P+3BXS	2P
23	5BXS	0P
24	1BX	0P
25	6BXS	0P
26	9P=32ROLLS	9P
Sheet1044

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT(--(LEFT(B2:B26,LEN(B2:B26)-1)))
B2:B26	B2	=IF(ISERR(SEARCH("P",A2)),"0P",TRIM(IF(RIGHT(A2)="P",RIGHT(SUBSTITUTE(SUBSTITUTE(A2,"=",REPT(" ",99)),"+",REPT(" ",99)),99),LEFT(SUBSTITUTE(SUBSTITUTE(A2,"=",REPT(" ",99)),"+",REPT(" ",99)),99))))

 

[rvill189]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would employ a user-defined function for this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below, adjusting for whatever your actual table name is.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Pallets(r As Range) As Long
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(.+?)(\d+P)"
    Pallets = Evaluate(Replace(.Replace(" " & Join(Application.Transpose(r)) & " 0P", "+$2"), "P", ""))
  End With
End Function

rvill189.xlsm
	A	B	C	D	E	F
1	LABEL Quantity	PHYSICAL Quantity			Pallet total:	159
2		8P
3		1P
4		23BXS=2P
5		2P
6		1P+33BXS
7		44P
8		40P
9		2P
10		11P
11		10P
12		5BXS
13		9P=32ROLLS
14		10P
15		1BX
16		6BXS
17		2P+17BXS
18		5P
19		10P
20		2P+17BXS
21
22
Sheet1

Cell Formulas
Range	Formula
F1	F1	=Pallets(Table2[PHYSICAL Quantity])

Thank you for your response,

I return the below error after entering the VBA code and formula:

 

[kweaver]

You want:

=pallets(ProductPriceList[PHYSICAL Quantity])