Combining Match and SUMIF

[klxracer]

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

[Batman]

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.

[Domenic]

Assuming that J5:BB15 contains your data, and that J5:BB5 contains your headers/labels, try the following...

=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!

[klxracer]

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.

[ally.mateo]

Hi,

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

[AAE]

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.

ALWAYS start a new thread for your questions. You may, if you find it helpful, provide a link back to this (or any other) thread.

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