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.

 

[rvill189]

Hi! This worked like a charm at work. Thanks to all for the great work.

 

[Peter_SSs]

Good news! Thanks for letting us know.

 

[rvill189]

Hello,

The formulas are still working and create a lot of visibility for us, thanks again. I have an formula on Column H that calculates the total "Days in Warehouse" against the date it was received; I noticed that the formula does not copy when someone inserts a row anywhere in the middle of the spreadsheet. As you can see below in cell H12, the formula did not populate/copy when a new row was inserted.

IMPORTS INVENTORY PULLMAN 03-04-2022(AutoRecovered).xlsx
	G	H
9	RECEIVE DATE	DAYS IN WAREHOUSE
10	2/28/2022	16
11	2/3/2022	41
12	1/1/2022
13	3/10/2022	6
Product Price List

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G554:H1048576,F5:G5,F8:G9	Cell Value	contains "completo"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G9	Any value
H9:H13	Any value

Given the previous application with the helper column to constantly calculate pallets, is there a way to do the same for date received - where the formula would not disappear if a new row was inserted?

 

[Peter_SSs]

I noticed that the formula does not copy when someone inserts a row anywhere in the middle of the spreadsheet.

Instead of inserting a whole row in the worksheet, can you just insert a row in the table? That way the formulas should auto-populate.

.. or right-click in the table ..

 

[rvill189]

Instead of inserting a whole row in the worksheet, can you just insert a row in the table? That way the formulas should auto-populate.

View attachment 60274


.. or right-click in the table ..

View attachment 60275

Thanks,

i have to investigate further. They populate when inserting, however the spreadsheet that is sent out to the team sometimes has those cells empty.