Formula Help please

[JohnCooper00000]

Afternoon Guys

After some advice ive been racking my brains but cant come up with a solution 

I have an excel sheet that I use to track which items have been installed & tested

In Column H is a list of items all starting with P or U some installs require a double items hence the numbers P-06.01.02 / P 07.01.02 are in the column.

Is there a way to count how many letter P and Letter U's are in the column based on a blank date in Column R ?

As always any help will be greatly appreciated

[#2 · Accepted solution]

@JohnCooper00000 

Does this do what you want?

=SUM((LEN(H2:H489)-LEN(SUBSTITUTE(SUBSTITUTE(H2:H489,"P",""),"U","")))*(P2:P489=""))

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

[#3]

@Hans Vogelaar Thankyou, can is there a way to filter per village from Column A
After entering the formula i end up with 342 but i would like to filter on Column A (villages) and this would tell me how many P & U are left to install in the village.

Thanksyou

[#4]

@JohnCooper00000 

Use this, if necessary again confirmed with  Ctrl+Shift+Enter.

=SUMPRODUCT((LEN(H2:H489)-LEN(SUBSTITUTE(SUBSTITUTE(H2:H489,"P",""),"U","")))*(P2:P489="")*(SUBTOTAL(103,OFFSET(H2,ROW(H2:H489)-ROW(H2),0,1))))