# Required SUM

##### Member
Respected Excel Guru's,

Required SUM for given range. Details explained in attached Sample File contains 2 Sheets.

Regards!

Suhas

#### Attachments

• 16 KB Views: 4

#### pecoflyer

##### 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

#### bosco_yip

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

=SUMIF(A:A,U5,B:B)+SUMIF(C:C,U5,D:D)+SUMIF(E:E,U5,F:F)+SUMIF(G:G,U5,H:H)+SUMIF(I:I,U5,J:J)

2] In "Sheet2", I2 formula :

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

Last edited:

##### Member
Thanx very much pecoflyer Sir.

Now I add the expected result in both the sheets.

Regards!

#### Attachments

• 18.3 KB Views: 4

#### pecoflyer

##### Active Member
I suppose Bosco understood your query. I still don't. I'll leave you in his ( her) capable hands

#### vletm

##### Excel Ninja
Based Sheet2:
for cells J2 `=SUBSTITUTE(MID(H2,2,9)," - ","")*1` and copy down

above sample with cell J3.
... and that total sum You seems to know.

... or what would You try to find out?

##### Member
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

##### Excel Ninja
Your: Now I want the solution for Sheet 1.
Where have You explain what do You want for Sheet 1?

##### Member
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.

Regards!

Suhas

#### Attachments

• 14.9 KB Views: 4

#### vletm

##### Excel Ninja
Do You know - There is less challenges after know - what some would expect?
... based Your sample data ... press [ Do It ]-button.

#### Attachments

• 22.5 KB Views: 3