Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Combining Match and SUMIF

1. Senior Member
Join Date
5th January 2005
Location
Wellington, New Zealand
Posts
136

## Combining Match and SUMIF

Hopefully this is something that has been done before, although I couldn't find it in searching this morning.

I need to do a SUMIF on a selection of data where the range is in column J, and the sum range is columns K through BB depending on another criteria (month) which is held in another cell. What I want to do is embed a MATCH statement into a SUMIF, or use some other formula that achieves the same result.

Here's how it looks as a formula:

=SUMIF(RetData!\$J:\$J,\$C16,Match(\$E\$5,RetData!\$J\$5:\$BB\$5),False))

Any suggestions? Hopefully the description and the formula show what I am trying to achieve.

Paul

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Combining Match and SUMIF

Hi klxracer,

Have a look at the example in the workbook attached. It uses a Dynamic Named Range called Values, which in turn uses the Match function to find the column to use.

The months are a static range called Months and the data is another Dynamic Named Range called Data. By using both of these two as DNRs it ensures that the value ranges for the SumIf are the same.

The formula in D1 is then just a straightforward SumIf using the range names.

Hope this helps.

Regards,
Batman.

Excel Video Tutorials / Excel Dashboards Reports

3. OzMVP
Join Date
4th July 2004
Location
Posts
2,371

## Re: Combining Match and SUMIF

=SUMIF(RetData!\$J\$6:\$J\$15,\$C16,INDEX(RetData!\$K\$6:\$BB\$15,0,MATCH(\$E\$5,RetData!\$K\$5:\$BB\$5,0)))

See the attached file.

Hope this helps!

Excel Video Tutorials / Excel Dashboards Reports

4. Senior Member
Join Date
5th January 2005
Location
Wellington, New Zealand
Posts
136

## Re: Combining Match and SUMIF

Thanks guys for your help - looks as though the problem is solved! Now I just need to replicate for the 8 different IF driven scenario's, quite comfortable with that though.

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
9th February 2011
Posts
11

## Re: Combining Match and SUMIF

Hi,

I couldn't replicate the column lookup for sum_range in a sumifs formula. Any help?

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Combining Match and SUMIF

Hello ally.mateo,

Posting your question in threads started by others is a violation of the Forum Rules and is known as thread hijacking. Posting solutions is acceptable.

Start a new thread and be sure to give it a title that complies with the following guidelines:

Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your thread content or overall objective using ONLY search friendly key words.

• The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
• The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (colons, semi-colons, slashes, etc.)
• The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

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
•
porno