< Go Back

Excel - COLUMN Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.