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.
