SUMIF with dynamic sum range

I have a variable (name) in one worksheet and I want to look it up from another worksheet and sum all values against this variable for a range of months (again variable).
I want the user to select the "name" in a cell and specify the months in another set of cells i.e. "Month from" and "Month to" and I want a formula that will look for the name in a list and sum the values corresponding to all the months including the "Month from" and "Month to". The added difficulty is that the name can have multiple entries in the list and hence I have to use SUMIF.

I have tried a combination of SUMIF, OFFSET, MATCH, HLOOKUP funtions, but I am struggling as the sumif funtion is adding only the values in one column and not in multiple columns and my sum range is a dynamic range depending on the user selection.

Would be grateful for some help. Thanks!!

I'm not sure why you would need more than just SUMIFS.

Could you post a sample workbook?

BSB.

Many thanks, I have attached a sample. The selection worksheet shows the variables and the Data worksheet has the data to be looked up

This is fantastic! It works and the formula is so simple. I had some #N/As in my name list which were causing the formula not to work, but after eliminating them it works perfectly fine.
Thanks very very much BSB, you saved me from having to work over xmas :-)

You could wrap the whole thing in IFERROR to take care of the N/A errors....

Happy to help
Have a good Christmas

BSB

Don't forget to mark the thread as SOLVED.

Yes!! this works as well. Many thanks for this.

I've just two simple ways of doing this :-)

How do I do that? I see people prefix solved to the thread title - how can I edit the title? Thanks

Originally Posted by msp_excel

How do I do that? I see people prefix solved to the thread title - how can I edit the title? Thanks

Have a look here:
http://www.excelforum.com/faq.php