SUMIFS with sum range dependent on another cell value

**Nemetski** · 09-13-2017, 07:11 AM

I'm interested in seeing if anyone knows of a way to change the sum_range on a SUMIFS with values from another cell. For example, let's say that the SUMIFS is using a sum_range on a different worksheet:

=SUMIFS(Base!$AK:$AK,Base!$O:$O,$B9)

I want to be able to change the $AK by entering another column reference in another cell (let's say D6). I've tried using INDIRECT in the sum_range function and get the "We found a problem with this formula" dialogue box.

Help me, VBA Express. You're my only hope.

**SamT** · 09-13-2017, 07:36 AM

Have you tried "Evaluate" and "[...]"?

Maybe you should be asking, "How do I get this certain result?" Rather than asking, "How do I fix this tool I made?"

**Nemetski** · 09-13-2017, 07:47 AM

I believe that's what I was doing. My intent was to get help performing the function rather than to say "This is the formula I made. Make it work and don't rewrite it."

I would be glad to use Evaluate on the cell, if such were possible. In my experience, however, I've been unable to get Excel to accept a formula once the "We found a problem with this formula" dialogue box comes up; I have to escape out of the cell. Therefor, I can't Evaluate the cell with the new formula.

I'm unfamiliar with the function [...]. If you could provide some background, I would be grateful. My internet search for this function was rather unsuccessful.

**SamT** · 09-13-2017, 08:12 AM

My intent was to get help performing the function

I kow, but you never told us the Functionality you need in your workbook, You only mentioned the SUMIFS() Function, which is used in the Formula "tool" you made. IOW, That Formula is a tool to do the Work you need done.

What's the Work you need to accomplish?

Excel XP doesn't have a SUMIFS() Function,but I bet someone here can write a VBA User Defined Function that you can use exactly as you do the SUMIFS but with the added functionality you need

=MYCUSTOMSUMIFS(A1,Base!$O:$O,$B9)