SOLVED

Formula Help please

%3CLINGO-SUB%20id%3D%22lingo-sub-2878958%22%20slang%3D%22en-US%22%3EFormula%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2878958%22%20slang%3D%22en-US%22%3E%3CP%3EAfternoon%20Guys%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20some%20advice%20ive%20been%20racking%20my%20brains%20but%20cant%20come%20up%20with%20a%20solution%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20sheet%20that%20I%20use%20to%20track%20which%20items%20have%20been%20installed%20%26amp%3B%20tested%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Column%20H%20is%20a%20list%20of%20items%20all%20starting%20with%20P%20or%20U%20some%20installs%20require%20a%20double%20items%20hence%20the%20numbers%20P-06.01.02%20%2F%20P%2007.01.02%20are%20in%20the%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20count%20how%20many%20letter%20P%20and%20Letter%20U's%20are%20in%20the%20column%20based%20on%20a%20blank%20date%20in%20Column%20R%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JohnCooper00000_0-1635090556320.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F319593i9FC6FD5024B4895D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JohnCooper00000_0-1635090556320.png%22%20alt%3D%22JohnCooper00000_0-1635090556320.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20always%20any%20help%20will%20be%20greatly%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2878958%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2878974%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2878974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1075306%22%20target%3D%22_blank%22%3E%40JohnCooper00000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20this%20do%20what%20you%20want%3F%3C%2FP%3E%0A%3CP%3E%3DSUM((LEN(H2%3AH489)-LEN(SUBSTITUTE(SUBSTITUTE(H2%3AH489%2C%22P%22%2C%22%22)%2C%22U%22%2C%22%22)))*(P2%3AP489%3D%22%22))%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20Microsoft%20365%20or%20Office%202021%2C%20confirm%20the%20formula%20with%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2879158%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2879158%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20Thankyou%2C%20can%20is%20there%20a%20way%20to%20filter%20per%20village%20from%20Column%20A%3CBR%20%2F%3EAfter%20entering%20the%20formula%20i%20end%20up%20with%20342%20but%20i%20would%20like%20to%20filter%20on%20Column%20A%20(villages)%20and%20this%20would%20tell%20me%20how%20many%20P%20%26amp%3B%20U%20are%20left%20to%20install%20in%20the%20village.%3CBR%20%2F%3E%3CBR%20%2F%3EThanksyou%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2879322%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2879322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1075306%22%20target%3D%22_blank%22%3E%40JohnCooper00000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20this%2C%20if%20necessary%20again%20confirmed%20with%26nbsp%3B%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((LEN(H2%3AH489)-LEN(SUBSTITUTE(SUBSTITUTE(H2%3AH489%2C%22P%22%2C%22%22)%2C%22U%22%2C%22%22)))*(P2%3AP489%3D%22%22)*(SUBTOTAL(103%2COFFSET(H2%2CROW(H2%3AH489)-ROW(H2)%2C0%2C1))))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 ?

 

JohnCooper00000_0-1635090556320.png

 

 

As always any help will be greatly appreciated

 

 

3 Replies
best response confirmed by JohnCooper00000 (New Contributor)
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.

@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

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