# Sumif help required

#### golf_man

##### New Member
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

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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)

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

You're welcome.

Replies
13
Views
208
Replies
1
Views
180
Replies
20
Views
624
Replies
2
Views
307
Replies
3
Views
213

1,203,600
Messages
6,056,205
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back