Sumifs

[bgonen]

Excel Workbook
	A	B	C	D	E	F	G
1	Group	Subgroup	Department	Jan	Feb	*	*
2	Americas	Energy	Oil	1	1	*	*
3	Americas	Medical	Xray	2	1	*	*
4	Americas	Energy	Wind	3	2	*	*
5	*	*	*	*	*	*	*
6	*	*	*	6	4	*	*
7	*	*	*	*	*	*	*
8	Variable:	Americas	*	=SUMIFS(A2:A4,B2:B4,C2:C4,B8,D2:D4)	*	*	*
Sheet1

I am trying to set a formula that will total columns D if I only change the variable cell: B8 (B8 can be Americas, Medical,Xray etc)
Any suggestions?

 

[Marcelo Branco]

Maybe this

Formula in D8
=SUMPRODUCT(D2:D4,--(ISNUMBER(SEARCH($B$8,$A$2:$A$4&$B$2:$B$4&$C$2:$C$4))))

copy across

HTH

M.

 

[Aladin Akyurek]

Excel Workbook
	A	B	C	D	E	F	G
1	Group	Subgroup	Department	Jan	Feb	*	*
2	Americas	Energy	Oil	1	1	*	*
3	Americas	Medical	Xray	2	1	*	*
4	Americas	Energy	Wind	3	2	*	*
5	*	*	*	*	*	*	*
6	*	*	*	6	4	*	*
7	*	*	*	*	*	*	*
8	Variable:	Americas	*	=SUMIFS(A2:A4,B2:B4,C2:C4,B8,D2:D4)	*	*	*
Sheet1

I am trying to set a formula that will total columns D if I only change the variable cell: B8 (B8 can be Americas, Medical,Xray etc)
Any suggestions?

D8, control+shift+enter, not just enter:

=SUM(IF($A$2:$C$4=$B8,D$2:D$4))

 

[bgonen]

Thank you very much both

I tried the Array formula Aladin suggested and it works fine for me (I'm sure the sumproduct works fine but I need to improve my Array knowledge)

 

[Aladin Akyurek]

Thank you very much both

I tried the Array formula Aladin suggested and it works fine for me (I'm sure the sumproduct works fine but I need to improve my Array knowledge)

You are welcome. Thanks for providing us feedback.