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.

 

[Peter_SSs]

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

Two things.

1. I'm guessing that you pasted the vba code in the wrong place. Check that it is in a standard module, not a worksheet module.

2. The formula in the worksheet is incorrect - you have extra text in there. It should be

=Pallets(ProductPriceList[PHYSICAL Quantity])

 

[rvill189]

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

Thank you!

Your response worked. The actual spreadsheet is well over 500 rows, and I leave it as a table should warehouse auditors need to add additional entries.

The formula in D1 does not add the P values of additional row entries. How can I make it so that D1 will constantly update when a row is removed or added?

 

[Peter_SSs]

To have the helper column you could add a new column to your table like this.
Hide the new column if you want.

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

Cell Formulas
Range	Formula
F1	F1	=SUM(ProductPriceList[Pallets])
C2:C20	C2	=AGGREGATE(14,6,RIGHT(LEFT([@[PHYSICAL Quantity]]&"0P",FIND("P",[@[PHYSICAL Quantity]]&"0P")-1),{1,2,3,4,5})+0,1)

 

[jtakw]

Thank you!

Your response worked. The actual spreadsheet is well over 500 rows, and I leave it as a table should warehouse auditors need to add additional entries.

The formula in D1 does not add the P values of additional row entries. How can I make it so that D1 will constantly update when a row is removed or added?

You can do 1 of 2 things, since you're likely to "Hide" the helper column, you can Pre-populate the helper with the B formula, and use the D formula as-is, or you can use the updated formula in D1, array-entered.

Book3.xlsx
	A	B	C	D
1	PHYSICAL Quantity		Pallet total:	183
2	1P	1P		183
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
27		0P
28		0P
29		0P
30		0P
Sheet1044

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT(--IFERROR(LEFT(B2:B100,LEN(B2:B100)-1),0))
D2	D2	=SUMPRODUCT(--LEFT(B2:B100,LEN(B2:B100)-1))
B2:B30	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))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rvill189]

Wow.. this is perfect. I can see its appli

To have the helper column you could add a new column to your table like this.
Hide the new column if you want.

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

Cell Formulas
Range	Formula
F1	F1	=SUM(ProductPriceList[Pallets])
C2:C20	C2	=AGGREGATE(14,6,RIGHT(LEFT([@[PHYSICAL Quantity]]&"0P",FIND("P",[@[PHYSICAL Quantity]]&"0P")-1),{1,2,3,4,5})+0,1)

Wow! This is amazing.

I will likely keep the "Pallet Total" cells aside from the table; however, is it at all possible to enter a formula in the header of a table? I have tried in previous circumstances and the formula disappears altogether and returns a 0.

Otherwise, this is what I was looking for.

 

[Peter_SSs]

I will likely keep the "Pallet Total" cells aside from the table;

Why? Having it in the table (& hidden if you want) is the easy way to get the automatic expansion/contraction if the table changes size.

is it at all possible to enter a formula in the header of a table?

No, I don't believe so.

BTW, did you get the udf version working?

 

[rvill189]

Why? Having it in the table (& hidden if you want) is the easy way to get the automatic expansion/contraction if the table changes size.


No, I don't believe so.

BTW, did you get the udf version working?

I agree, hidden should do it.

I did not get the udf version working. The spreadsheet is updated on a .csv spreadsheet via Tablet and I do not think this would be able to convert/open correct on their Tablet.

I will test this out on the actual spreadsheet and keep you posted!

Thanks so much.

 

[Peter_SSs]

I do not think this would be able to convert/open correct on their Tablet.

OK, that could well be the case.

BTW, it would still be a good idea to update your version details (see post #7) since different versions have different functions and features available to them.

 

[rvill189]

OK, that could well be the case.

BTW, it would still be a good idea to update your version details (see post #7) since different versions have different functions and features available to them.

Thanks so much,

I missed that note. Updated just now.

 

[Peter_SSs]

Updated just now.

Great, thanks for that.