Index-Match For Sum_range In Sumifs Formula?

[emus007]

Hi,

I've used an index-match formula as the sum_range in a sumif formula, but when I try to do the same with a sumifs formula I return a #VALUE! error.

Is it possible to do this normally?

Thanks,
Elu.

 

[Aladin Akyurek]

Hi,

I've used an index-match formula as the sum_range in a sumif formula, but when I try to do the same with a sumifs formula I return a #VALUE! error.

Is it possible to do this normally?

Thanks,
Elu.

Would you post what you have tried?

 

[Firefly2012]

Correctly formed there is no reason that should not work.

Can you post what you have tried that isn't working?

 

[emus007]

I have table which has labour information from the current financial
year, and one of my colleagues is wanting to look at some of the
information a different way. The following is an example of a portion of
the information and what I'm trying to achieve...

I have the following data on Sheet1 in cells A1:G18:

Site	Type	Organisation Unit	Wk30	Wk31	Wk32	Wk33
AL		AL Albany				7.25
AL		AL Mainstream	43.57	23.17	107.37	13.82
AL		AL Bar	66.08	48.20	49.53	28.08
AL		AL Floor	275.57	238.50	203.50	173.03
AL		AL POS	418.72	422.43	348.38	303.97
AL		AL Management	140.98	129.78	159.17	134.30
AL	PROJ	AL Projection	111.05	109.73	97.88	86.05
ALGC		ALGC Gold Class
ALGC		ALGC Management	100.42	116.32	89.58	82.55
ALGC		ALGC Service	457.97	476.93	384.33	308.10
BW		BW NewMarket / Broadway	8.00	32.00	8.00
BW		BW Mainstream	199.82	55.85	41.35	27.27
BW		BW Bar	25.85	21.93	20.23	25.72
BW		BW Floor	92.87	84.72	85.07	76.38
BW		BW POS	121.73	210.75	203.20	167.70
BW		BW Management	117.97	126.37	108.43	133.33
BW	PROJ	BW Projection	99.60	86.65	95.17	104.53

<colgroup><col><col><col><col span="4"></colgroup><tbody>
</tbody>

On Sheet2 I have the following table in cells A1:E4:
<table style="width: 445px;" border="0" cellSpacing="0" cellPadding="0"><colgroup><col span="5"></colgroup><tbody></tbody>

PROJ
WK END	MONTH	WEEK	AL	BW
2013-01-30	JUL	Wk30
2013-02-06	JUL	Wk31

<colgroup><col span="5"></colgroup><tbody>
</tbody>

On Sheet2 in cell D3 I've tried the formula:
=SUMIFS(INDEX('Sheet1'!$D$1:$G$18,0,MATCH('Sheet2'!$C3,'Sheet1'!$D$1:$G$1,0)),'Sheet1'!$A:$A,'Sheet2'!D$2,'Sheet1'!$B:$B,'Sheet2'!$A$1)

What
I'm after is a formula that I can copy from cell D3 to cells D4, as
well as to C3:C4 that sums information from Sheet1 when the Site &
Type match from columns A&B, and the sum_range (and I hope this
makes sense) is variable, dependent on the week from Sheet2.

I'm not sure if the sumifs formula is the best one to use or if I've written it correctly.

 

[Firefly2012]

You need to ensure the criteria ranges are the same size (number of rows in this case) as the Sum range, but you are using whole column ('Sheet1'!$A:$A) refs which leads to a mismatch and hence the #VALUE! error. Try:

=SUMIFS(INDEX('Sheet1'!$D$1:$G$18,0,MATCH('Sheet2'!$C3,'Sheet1'!$D$1:$G$1,0)),'Sheet1'!$A$1:$A$18,'Sheet2'!D$2,'Sheet1'!$B$1:$B$18 ,'Sheet2'!$A$1)

 

[emus007]

Of course! That's perfect.

You're a legend. Thank you very much!!

 

[Firefly2012]

You're welcome