# SUMIF formula to identify column matching a named cell

1. ## SUMIF formula to identify column matching a named cell

Hi all,

Hoping someone may be able to help with this query.

I have a summary sheet and a data sheet. On the data sheet are the months Jan-Dec in columns B-M and names in column A (eg Caroline). On the summary sheet I have created a drop down list to enable me to choose the month I want and labelled the cell "CurrentMonth" in the Name Manager.

Is there a formula I can write that will know to sum everything in the "CurrentMonth" column for a specified name (eg Caroline)?

I have written the below but it is giving me a #REF! error.

=SUMIF(INDIRECT("'"&"Data Sheet"&"'!\$A:\$A"),"Caroline",INDIRECT("'"&"Data Sheet"&"CurrentMonth"))

Thanks!

2. ## Re: SUMIF formula to identify column matching a named cell

Make sure that the named range is given the workbook setting, not sheet, then try this:

=SUMPRODUCT(('Data Sheet'!A\$2:A\$1000="Caroline")*(CurrentMonth="January"),'Data Sheet'!B\$2:M\$1000)

or without a named range:

=SUMPRODUCT(('Data Sheet'!A\$2:A\$1000="Caroline")*('Data Sheet'!B\$1:M\$1="January"),'Data Sheet'!B\$2:M\$1000)

3. ## Re: SUMIF formula to identify column matching a named cell

Thank you that was really helpful. The second formula without the named range worked but not the first. The Name Manager shows that it is valid for the whole workbook..

I had wanted to use a named range in the formula so it would automatically update for a new month when i updated the drop down for the next month. Is that possible?

4. ## Re: SUMIF formula to identify column matching a named cell

Yes. Like this:

=SUMPRODUCT(('Data Sheet'!A\$2:A\$1000="Caroline")*('Data Sheet'!B\$1:M\$1=\$A\$1),'Data Sheet'!B\$2:M\$1000)

Just change \$A\$1 to the cell with the drop-down.

There is no need for named cells or named ranges.

5. ## Re: SUMIF formula to identify column matching a named cell

Thank you very much, that was worked perfectly

6. ## Re: SUMIF formula to identify column matching a named cell

You're welcome!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1