Oct 24 2021 08:50 AM
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
Oct 24 2021 08:58 AM
SolutionDoes 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.
Oct 24 2021 10:42 AM
Oct 24 2021 12:23 PM
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))))
Oct 24 2021 08:58 AM
SolutionDoes 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.