• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Required SUM


Active Member
- There is not the slightest explanation in your sheet
- You should explain what you are trying to do in words in your post. That way, members do not have to open your file to try to understand what you are trying to do
- Please also add manually calculated expected results


Excel Ninja
1] In "Sheet1", V5 formula :



2] In "Sheet2", I2 formula :

=SUMPRODUCT(0+SUBSTITUTE(LEFT(H2:H75,FIND(" out",H2:H75&" out")-1)," - ",""))

Last edited:


Excel Ninja
Based Sheet2:
for cells J2 =SUBSTITUTE(MID(H2,2,9)," - ","")*1 and copy down
Screenshot 2020-12-31 at 19.10.59.png
above sample with cell J3.
... and that total sum You seems to know.

... or what would You try to find out?
Thanx Pecoflyer Sir.
Also thank you Bosco Sir, given your SUMPRODUCT formula (Sheet 2) I am getting the Total of the column. Thank you.
Thank you very much Vletm Sir, I accomplish what I need by your given formula for Sheet 2.

Now I want the solution for Sheet 1.

Thank you all the Guru's and Happy New Year 2021.
Vletm Sir,

I am attaching a file named as New contining 2 sheets.

1st is named as Old.

2nd is named as Desired Result, which I have done it manually.

Thanking you.