Sumifs with multiple criteria and sumranges

[vanbasten007]

Hi guys,

I need your help with the Sumifs function. Please see the attached example. What I am trying to do is depending on what period it is, have my sumifs function add up more than one sumrange. For example, if the current period is 3, sum columns containing Period 1, Period 2, and Period 3 (three separate columns) and use multiple criteria, for example, do this for dept 150 and cost category Admin. I am sure there is a way to do this but can't yet figure it out. I can only do sumifs with the offset function for any one period.

Thank you

[david0985]

Hi,

Take a look at my solution. I had to calculate your result in two steps, first using the choose function and then using a sumifs.

David

[vanbasten007]

Thanks David, I guess I can also do sum(offset(...)) but I am curious if there is a way to do it in one step.

[FDibbins]

Try this...
=SUMPRODUCT(($A$4:$A$16="Admin")*($B$4:$B$16=150)*(OFFSET($D$4,0,0,13,B18)))

[Jacc]

Here are three different solutions. The last one is what you are after I guess.

[Tony Valko]

Here's another one...

Data Range

	A	B	C	D	E	F	G
3	Category	Department	Account	Jul	Aug	Sep	Oct
4	Consulting	340	84100	$3,240	$3,240	$3,240	$3,240
5	Admin	150	55000	$1,215	$1,215	$1,215	$1,215
6	Consulting	250	89000	$17,500	$0	$0	$0
7	Consulting	150	83250	$5,953	$5,953	$5,953	$5,953
8	Admin	150	51210	$1,287	$1,287	$1,287	$1,287
9	Consulting	160	87000	$145	$145	$145	$145
10	Admin	120	51410	$281	$281	$281	$281
11	Consulting	150	88654	$660	$660	$660	$660
12	Admin	150	51110	$5,953	$5,953	$5,953	$5,953
13	T&M	180	61600	$1,287	$1,287	$1,287	$1,287
14	Admin	150	51310	$145	$145	$145	$145
15	T&M	280	61400	$281	$281	$281	$281
16	Admin	150	51510	$660	$660	$660	$660
17
18	Current Period	3	27777.83
19	Category	Admin
20	Dept	150

This array formula** entered in C18:

=SUM(IF(A4:A16=B19,IF(B4:B16=B20,D4:INDEX(D4:G16,0,B18))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[FDibbins]

Stealing from Tony (the last part), try this normal formula...
=SUMPRODUCT(($A$4:$A$16="Admin")*($B$4:$B$16=150)*(D4:INDEX(D4:G16,0,B18)))