SUM & SUMIFS formula not returning correct value

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Ive attached a screen shot, of the filtered data and the sheet im populating
Data1.PNG

Data2.PNG


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)

Sheet1.PNG


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
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

Be sure to follow & read the link at the end of the rule too!

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
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
How about
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"}))
as you had before.
 

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

data3.PNG

sheet2.PNG

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
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Impossible to say as I have no idea what cells are what in the lower pic.
 

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I apologize didn't realise i missed the cells etc , here it is with cells and formula

Sheet3.PNG
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top