# SUM & SUMIFS formula not returning correct value

#### juca73

##### New Member
Hi

I am using the following formula

=SUM(SUMIFS(Data!\$AG\$7:\$AG\$1048576,Data!\$D\$7:\$D\$1048576,Sheet1!\$F\$2,Data!\$O\$7:\$O\$1048576,">="&\$D40,Data!\$O\$7:\$O\$1048576,"<="&\$E40,Data!\$M\$7:\$M\$1048576,{"Amber","Green"},Data!\$N\$7:\$N\$1048576,{"Amber","Red"}))

The value returned is not the same as when i filter it on the data sheet

Sheet 1 = returns 15.00
Data Sheet = returns 10.28

There were some blank cells in column AG on the data sheet, i have inserted 0.00, thinking
the blank cells would be causing the fault, turns out not to be the case

Any ideas?

Thanks

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### RAJESH NATH

##### Board Regular
can you share your excel sheet

#### Joe4

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

#### juca73

##### New Member
Ive attached a screen shot, of the filtered data and the sheet im populating

The above two images are from the data set im using and as you can see the expected return should be 10.28 (Column AG)

This is the sheet i want populating, as you can see in Column N and boxed green is the return i get using the same filter criteria on the data set, its a different value return, 15.00 when it should be 10.28.

I would really appreciate if someone can assist, Is the formula incorrect? it works perfectly when i only have one colour selected in Home and Away i.e amber v amber.

#### Fluff

##### MrExcel MVP, Moderator

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Cross posted at: sumifs formula not returning correct value
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=SUM(SUMIFS(Data!\$AG\$7:\$AG\$1048576,Data!\$D\$7:\$D\$1048576,Sheet1!\$F\$2,Data!\$O\$7:\$O\$1048576,">="&\$D40,Data!\$O\$7:\$O\$1048576,"<="&\$E40,Data!\$M\$7:\$M\$1048576,{"Amber","Green"},Data!\$N\$7:\$N\$1048576,{"Amber";"Red"}))``

#### juca73

##### New Member

Ive managed to adjust and add the ; in the last part of the formula, however another problem i have now is this

When i filter the same criteria in the database only one result comes up for the row highlighted yellow which is the correct value, however there are 3 more values returning the same value and these cant be filtered at the odds values, they dont exsist, i suspect they are populating because of the "Red" value, how can i remedy this?

#### Fluff

##### MrExcel MVP, Moderator
Impossible to say as I have no idea what cells are what in the lower pic.

#### juca73

##### New Member
I apologize didn't realise i missed the cells etc , here it is with cells and formula

#### Fluff

##### MrExcel MVP, Moderator
That's a different formula!!
Also looking at the images what surprises me (assuming you have the same formula in the entire column) is that some cells in col N do not return 0.18
Your criteria of C2 & H10 are fixed so the only thing that changes is the value in cols D & E, which are all the same in that image.

Replies
1
Views
57
Replies
3
Views
60
Replies
6
Views
260
Replies
5
Views
110
Replies
8
Views
77

1,127,656
Messages
5,626,133
Members
416,164
Latest member
Zhevr

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