a concatenate function inside of a sumif

**Aguhr** · 09-19-2012, 03:44 PM

I understand there is not concatenate in the example below - its because I havent figured out how to make it work yet.

I have a row with the names of tabs in a file
I want to create a sumif function that will automatically pick up the name of the file.

example
3156 2689 3032 3094 - this is just 4 of 350 columns

my current equation which i have to manually change the 3156 to 2689 for the next column - all other parts of the equation stay the same.

=SUMIF('3156'!$B:$B,Summary!$B3,'3156'!$P:$P)

Any help would be awesome - i find myself in this situation a lot and haven't found a answer to this via google search yet.

thanks

**bentleybob** · 09-19-2012, 10:08 PM

Assuming that your tab names are in the Summary tab, starting in cell B1 and moving across, then the following should work:

Please Login or Register  to view this content.

**benishiryo** · 09-19-2012, 10:19 PM

hi Aguhr, welcome to the forum. so i'm just assuming your "3156" is in E1 & you are going to copy the formulas by column, you can try:

=SUMIF(INDIRECT("'"&E1&"'!$B:$B"),Summary!$B3,INDIRECT("'"&E1&"'!$P:$P"))

change the bold reference to your "3156" is. hope that helps. otherwise, upload a sample excel file for us. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon

by the way, you should not use the whole column as reference. slows down Excel

**Aguhr** · 09-20-2012, 09:09 AM

that works great - thank you

I've also changed it from the entire column to a specific range - thansk for that. I was lazy.

**Cutter** · 09-20-2012, 11:33 AM

@ Aguhr

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.