**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need assistance with combining sumifs and indirect. I want to find the worksheet with the same name as cell E14 and get the sum of the column titled "Login Defects".

Solved by D. D. in 22 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
26/02/2018 - 07:05

Hello :)

User
26/02/2018 - 07:05

hi

Excelchat Expert
26/02/2018 - 07:06

Are you able to share your spreadsheet?

User
26/02/2018 - 07:06

unfortunately no I can't :(

User
26/02/2018 - 07:06

I wish! I'm sure it would be easier

Excelchat Expert
26/02/2018 - 07:06

No worries. I'm going to work on something for you now.

User
26/02/2018 - 07:06

thank you!

Excelchat Expert
26/02/2018 - 07:07

Is Column Defects in different columns on each tab?

User
26/02/2018 - 07:07

yes

Excelchat Expert
26/02/2018 - 07:09

Okay, what is the furthest column?

User
26/02/2018 - 07:10

BL

Excelchat Expert
26/02/2018 - 07:10

Okay, great. Thank you.

Excelchat Expert
26/02/2018 - 07:17

I'm close.

Excelchat Expert
26/02/2018 - 07:19

I can complete this for you, but it would require each tab to already have the sum of each column. Is there an issue with this solution?

User
26/02/2018 - 07:23

no not at all

User
26/02/2018 - 07:23

I can add something for that

Excelchat Expert
26/02/2018 - 07:23

Okay, perfect.

Excelchat Expert
26/02/2018 - 07:23

This will be your solution then.

Excelchat Expert
26/02/2018 - 07:23

What is the furthest amount of rows you have?

Excelchat Expert
26/02/2018 - 07:23

on any given sheet.

User
26/02/2018 - 07:24

For rows, that is going to vary throughout the year. More info is added each week

Excelchat Expert
26/02/2018 - 07:24

There has to be a limit though.

User
26/02/2018 - 07:24

let's say 10,000

User
26/02/2018 - 07:24

I don't think I'll have more than that on any one sheet

Excelchat Expert
26/02/2018 - 07:24

Okay, perfect. So this will be your formula.

Excelchat Expert
26/02/2018 - 07:24

One moment.

Excelchat Expert
26/02/2018 - 07:25

=sum(indirect(address(1,match("Login Defects",A1:BL1))):indirect(address(1000,match("Login Defects",A1:BL1))))

Excelchat Expert
26/02/2018 - 07:25

You will put that in A10,000

Excelchat Expert
26/02/2018 - 07:26

Then you will put this next formula in your sheet to find the sheet name.

Excelchat Expert
26/02/2018 - 07:26

=indirect("'"&E14&"'!A10000")

User
26/02/2018 - 07:26

perfect! THANK YOU SOOO MUCH!

Excelchat Expert
26/02/2018 - 07:26

=sum(indirect(address(1,match("Login Defects",A1:BL1))):indirect(address(11999,match("Login Defects",A1:BL1))))

Excelchat Expert
26/02/2018 - 07:27

Please use this formula instead of the first one.

Excelchat Expert
26/02/2018 - 07:27

I had to change the rows so that it counted up to 10000

Excelchat Expert
26/02/2018 - 07:27

Is there anything else I can help with?

User
26/02/2018 - 07:27

no that is all. thank you so much

Excelchat Expert
26/02/2018 - 07:27

You're very welcome. Please be sure to rate the session after you press end :)

Excelchat Expert
26/02/2018 - 07:27

Have a wonderful day!

User
26/02/2018 - 07:28

of course. you as well :)

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*