SUMIFS with sum range having multiple columns

[dstarns]

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
We are trying to use SUMIFS with the sum range running over a range of cells. In this example our range would be C2:D16.

=SUMIFS($C$2:$D$16,$A$2:$A$16,$G3,$C$1:$D$1,H$2)

In most of the examples I find on the Internet the sum range is only one column. So I'm not sure if SUMIFS would work here, if not can someone point me in a direction to solve this problem?

Thanks for the help in advance.

Dave

 

[Robert Mika]

[TABLE="width: 500"]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[/TABLE]
We are trying to use SUMIFS with the sum range running over a range of cells. In this example our range would be C2:D16.

=SUMIFS($C$2:$D$16,$A$2:$A$16,$G3,$C$1:$D$1,H$2)

In most of the examples I find on the Internet the sum range is only one column. So I'm not sure if SUMIFS would work here, if not can someone point me in a direction to solve this problem?

Thanks for the help in advance.

Dave

Excel Workbook
	A	B	C	D	E	F	G	H
1	258		b	b
2								b
3							a
4	a		123	123
5
6
7
8	a		12
Sheet1

 

[dstarns]

That worked like a charm... Took a bit to figure out what the formula does but thanks again for your quick response.

This community rocks.

Dave

 

[Robert Mika]

That worked like a charm... Took a bit to figure out what the formula does but thanks again for your quick response.

This community rocks.

Dave

You are welcome.
Array formula are very powerful as you.
Just google it to find out more.

 

[Bondi]

You are welcome.
Array formula are very powerful as you.
Just google it to find out more.

Would anyone know why I am getting the Value# when trying this one? It looks like a really helpful formula but can't get it to work.

Many thanks

 

[jtakw]

Hi,

Since this is an Array formula, Did you enter your formula by Control + Shift + Enter?

If you did, and it doesn't work for you, since this post is over three years old, you might want to start your own thread and post a detailed description of your data and what you want to achieve.

 

[cristian malos]

Hi,

I'm trying to sum up the values for teh same name in different columns, but I don't find out the right formula. Pivot table helps me only column by column. Could anyone help me with a solutin?
Thank you!

Example


[TABLE="width: 288"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]FEDERICA[/TD]
[TD="align: right"]3[/TD]
[TD] CRISTIAN[/TD]
[TD="align: right"]4[/TD]
[TD] CATHERINE[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]CRISTIAN[/TD]
[TD="align: right"]3[/TD]
[TD] LAURENT[/TD]
[TD="align: right"]4[/TD]
[TD] PAULE[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]CRISTIAN[/TD]
[TD="align: right"]3[/TD]
[TD] CRISTIAN[/TD]
[TD="align: right"]4[/TD]
[TD] CRISTIAN[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]CRISTIAN[/TD]
[TD="align: right"]3[/TD]
[TD] MONIQUE[/TD]
[TD="align: right"]4[/TD]
[TD] CRISTIAN[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]FEDERICA[/TD]
[TD="align: right"]3[/TD]
[TD] MONIQUE[/TD]
[TD="align: right"]4[/TD]
[TD] FLORENCE[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]MONIQUE[/TD]
[TD="align: right"]3,5[/TD]
[TD] MONIQUE[/TD]
[TD="align: right"]4[/TD]
[TD] FEDERICA[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[leahpatterson]

I have a SUMIFS formula that is returning a #SPILL! error. The formula is below:

=SUMIFS('enrollment'!$B$3:$B$68,'mkt'!!$AG7,'enrollment'!$J$3:$J$68,'enrollment'!$G$3:$G$68,'mkt'!!$AG1)

enrollment $B:$B is looking at Market City (all markets)
mtk AG7: specific market (Akron)
enrollment $J:$J is looking at enrollment
enrollment $G:$G Existing contract Yes/No
mtk AG1: Yes

I need help to get the formula not to SPILL

 

[Fluff]

You're formula makes no sense, as you seem to have the arguments in the wrong order.
However if you need help on this I would suggest that you start a new thread, rather than posting to a thread that is almost 6 years old.
I would also suggest that you post some sample data using the Xl2BB add-in