Sumif help required

[golf_man]

Hi

I am trying to understand why this formula isn't working and is returning a #VALUE! error. Basically I am just trying to sum the values by the date (running across the top) and based on 2 conditions. The first condition is in Column B in TABLE 2 and the second condition is in column C in TABLE 2 (with the same fields in TABLE 1 also).

The formula is on a separate sheet to the data (the formula starts in Cell D5 in the second table). The data is contained on a sheet called 'Brands' (Table 1) and the values I want to sum up are in this sheet starting in cell F6.

The formula is:

=SUMIFS(INDEX(Brands!$F$4:$X$1354,,MATCH(D$3,Brands!$F$3:$Q$3,0),1),Brands!$B$4:$B$1375,"="&B5,Brands!$E$4:$E$1375,"="&C5)

Thanks


Excel 2010

TABLE 1​	A​	B​	C​	D​	E​	F​	G ​
1​	​	​	​	​	​	​	​
2​	​	​	​	​	​	​	​
3​	Accounts​	Lookup​	Portfolio​	Brand​	​	Apr-15​	May-15​
4​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Volume​	​	​
5​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Total Issue Actual​	​	​
6​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Total Issue Target​	3,782​	3,693​
7​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Total Issue Variance​	​	​
8​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Revenue​	​	​
9​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Total Issue Revenue Actual​	​	​
10​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Total Issue Revenue Target​	£5,248​	£5,136​
11​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Total Issue Revenue Variance​	​	​
12​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Volume​	​	​
13​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Single Issue Purchase Actual​	​	​
14​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Single Issue Purchase Target​	532​	636​
15​	Metro - Sport​	Immediate + Metro​	Metro - Sport​	Top Gear​	Single Issue Purchase Variance​	​	​

Sheet1

Excel 2010

TABLE 2​	A​	B​	C​	D​	E​	F​
1​		​	​	​	​	​
2​		​	​	​	​	​
3​		​	​	Apr-15​	May-15​	Jun-15​
4​		Immediate + Metro​	Volume​	​	​	​
5​		Immediate + Metro​	Total Issue Actual​	#VALUE!​	​	​
6​		Immediate + Metro​	Total Issue Target​	#VALUE!​	​	​
7​		Immediate + Metro​	Total Issue Variance​	#VALUE!​	​	​
8​		Immediate + Metro​	Revenue​	#VALUE!​	​	​
9​		Immediate + Metro​	Total Issue Revenue Actual​	#VALUE!​	​	​

Sheet1

 

[Jonmo1]

SUMIFS requires all the ranges to be of equal dimensions.
Your INDEX is using a range of $F$4:$X$1354 (1351 Rows)
But the criteria ranges are $B$4:$B$1375 and $E$4:$E$1375 (1372 Rows)

They should all be the same.


Also.
Although this isn't the cause of the error, the Match function should use the same # of columns as the Indexed Range

Try
=SUMIFS(INDEX(Brands!$F$4:$X$1375,,MATCH(D$3,Brands!$F$3:$X$3,0),1),Brands!$B$4:$B$1375,"="&B5,Brands!$E$4:$E$1375,"="&C5)

 

[golf_man]

That's great, working now. A second pair of eyes always helps! Cheers Jonmo1.

 

[Jonmo1]

You're welcome.