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
 

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The formula changes row by row depending on what colour is populated in the cells, so the M column on the data base will be any of the combinations as indicated in columns G - I and this is the same for the data base column N and the colours indicated by columns J - L.

As mention the cell highlighted yellow is the correct return when using the filter on the database, the issue i have is that there are 3 cells below it that return the 0.18 value yet in the database these colour and min/max odds combinations do not exsist, yet the formula is still returning 0.18, im wondering is it because of the Red par of the formula in Data!$N$7:$N$1048576,{"*Amber";"*Red*"}))? even though the search should be for Amber & Red.
 

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"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
What is the formula in the cell that is correct?
 

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=SUMIFS(Data!$AG$7:$AG$1048576,Data!$D$7:$D$1048576,$C$2,Data!$O$7:$O$1048576,">="&$D20,Data!$O$7:$O$1048576,"<="&$E20,Data!$M$7:$M$1048576,$H$10,Data!$N$7:$N$1048576,$L$10)

This formula is returning the correct value, theres no SUM at the beginning as its only searching for one colour in database column M and one colour in database column N.

The incorrect values, or ones that repeat the 0.18 are searching for one colour in column M and 2 or all three colours from column N

=SUM(SUMIFS(Data!$AG$7:$AG$1048576,Data!$D$7:$D$1048576,$C$2,Data!$O$7:$O$1048576,">="&$D22,Data!$O$7:$O$1048576,"<="&$E22,Data!$M$7:$M$1048576,$H$10,Data!$N$7:$N$1048576,{"*Amber";"*Red*"}))

formula for the first incorrect value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
I've no idea why you are changing the formula on every row, it makes no sense.
That said, the only difference between the two formulae is one is looking for Red in col N, the other is looking for red or amber in col N, that's why they both return the same result.
 

juca73

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

ADVERTISEMENT

so there is no need to add the SUM part or include it in the ones without?

so what do i need to add to change the formula to search for red & amber? instead of red or amber
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
You will need to explain exactly what you are trying to do.
Also post some sample data from both sheets

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

ADVERTISEMENT

Heres a mini sheet ive done

Please note the yellow cell is an incorrect answer it should be 0.31 Not 1.31 as shown

Test2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Task is to return the values in Column N to the criteria for each row 1.Columns D & E 2.Column F (is changeable from F6) 3.Columns G-H can be a combination of all three ( need to be AND not OR ( i.e Amber AND Green AND Red) 4.Columns J-L same as No3 above.
2
3SeasonDateTimeLeagueHome TeamAway TeamHCACHomeBHJ League
42017Saturday, August 05, 201710:00:00J LeagueKofuG-OsakaRedGreen5.203.99Allsvenskan
52017Saturday, August 05, 201710:30:00J LeagueKashimaSendaiGreenAmber1.650.62Eliteserien
62017Saturday, August 05, 201711:00:00J LeagueAlbirex NiigataYokohama FMRedGreen3.60-1.00
72017Saturday, August 05, 201711:00:00J LeagueC-OsakaSapporoGreenRed1.700.67J LeagueHOME COLOURAWAY COLOUR
82017Saturday, August 05, 201711:00:00J LeagueIwataHiroshimaAmberRed2.80-1.00RankOdds CriteriaMin OddsMax OddsLeagueAmberGreenRedAmberGreenRedBetReturn
92017Saturday, August 05, 201711:00:00J LeagueKashiwaKobeGreenAmber2.000.95HOME2.013.00J LeagueAmberAmberBACK Home0.00
102017Saturday, August 05, 201711:00:00J LeagueKawasakiFC TokyoGreenAmber1.95-1.00HOME2.013.00J LeagueAmberGreenBACK Home0.00
112017Saturday, August 05, 201711:00:00J LeagueTosuShimizuGreenRed2.381.31HOME2.013.00J LeagueAmberRedBACK Home-1.00
122017Saturday, August 05, 201711:00:00J LeagueUrawaOmiyaAmberAmber1.51-1.00HOME2.013.00J LeagueAmberAmberGreenBACK Home0.00
132017Saturday, August 05, 201715:00:00AllsvenskanHalmstadsJonkopings SodraRedRed2.421.35HOME2.013.00J LeagueAmberAmberRedBACK Home0.00
142017Saturday, August 05, 201717:00:00AllsvenskanOrebroElfsborgAmberAmber2.64-1.00HOME2.013.00J LeagueAmberGreenRedBACK Home0.00
152017Saturday, August 05, 201717:00:00EliteserienRosenborgKristiansundGreenRed1.310.29HOME2.013.00J LeagueAmberAmberGreenRedBACK Home0.00
162017Sunday, August 06, 201714:00:00AllsvenskanHammarbyHackenGreenAmber2.52-1.00HOME2.013.00J LeagueAmberGreenRedAmberBACK Home0.00
172017Sunday, August 06, 201714:00:00AllsvenskanKalmar FFIFK GoteborgAmberAmber2.881.79HOME2.013.00J LeagueAmberGreenGreenBACK Home0.00
182017Sunday, August 06, 201714:00:00AllsvenskanSundsvallOstersunds FKRedAmber4.10-1.00HOME2.013.00J LeagueAmberGreenRedBACK Home0.31
192017Sunday, August 06, 201716:30:00AllsvenskanSiriusAIKGreenGreen3.65-1.00HOME2.013.00J LeagueAmberGreenAmberGreenBACK Home0.00
202017Sunday, August 06, 201717:00:00EliteserienOdds BKSogndalAmberAmber1.820.78HOME2.013.00J LeagueAmberGreenAmberRedBACK Home1.31
212017Sunday, August 06, 201717:00:00EliteserienSarpsborgHaugesundAmberAmber1.810.77HOME2.013.00J LeagueAmberGreenRdGreenRedBACK Home
222017Sunday, August 06, 201717:00:00EliteserienStabaekSandefjordRedRed1.83-1.00HOME2.013.00J LeagueAmberGreenRedAmberGreenRedBACK Home
232017Sunday, August 06, 201717:00:00EliteserienTromsoMoldeAmberAmber3.00-1.00
242017Sunday, August 06, 201717:00:00EliteserienVikingLillestromAmberAmber2.36-1.00
252017Sunday, August 06, 201719:00:00EliteserienAalesundsBrannAmberAmber2.82-1.00
262017Monday, August 07, 201718:00:00AllsvenskanDjurgardensMalmo FFGreenGreen3.25-1.00
272017Monday, August 07, 201718:00:00AllsvenskanNorrkopingAFC UtdAmberRed1.320.30
28
Data
Cell Formulas
RangeFormula
X9X9=SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N9,Data!$I$4:$I$27,"<="&$O9,Data!$G$4:$G$27,{"Amber"},Data!$H$4:$H$27,{"Amber"})
X10X10=SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N10,Data!$I$4:$I$27,"<="&$O10,Data!$G$4:$G$27,{"Amber"},Data!$H$4:$H$27,{"Green"})
X11X11=SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N11,Data!$I$4:$I$27,"<="&$O11,Data!$G$4:$G$27,{"Amber"},Data!$H$4:$H$27,{"Red"})
X12X12=SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N12,Data!$I$4:$I$27,"<="&$O12,Data!$G$4:$G$27,{"Amber"},Data!$H$4:$H$27,{"Amber","Green"})
X13X13=SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N13,Data!$I$4:$I$27,"<="&$O13,Data!$G$4:$G$27,{"Amber"},Data!$H$4:$H$27,{"Amber","Red"})
X14X14=SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N14,Data!$I$4:$I$27,"<="&$O14,Data!$G$4:$G$27,{"Amber"},Data!$H$4:$H$27,{"Green","Red"})
X15X15=SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N15,Data!$I$4:$I$27,"<="&$O15,Data!$G$4:$G$27,{"Amber"},Data!$H$4:$H$27,{"Amber","Green","Red"})
X16X16=SUM(SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N16,Data!$I$4:$I$27,"<="&$O16,Data!$G$4:$G$27,{"Amber","Green","red"},Data!$H$4:$H$27,{"Amber"}))
X17X17=SUM(SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N17,Data!$I$4:$I$27,"<="&$O17,Data!$G$4:$G$27,{"Amber","Green"},Data!$H$4:$H$27,{"Green"}))
X18X18=SUM(SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N18,Data!$I$4:$I$27,"<="&$O18,Data!$G$4:$G$27,{"Amber","Green"},Data!$H$4:$H$27,{"Red"}))
X19X19=SUM(SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N19,Data!$I$4:$I$27,"<="&$O19,Data!$G$4:$G$27,{"Amber","Green"},Data!$H$4:$H$27,{"Amber","Green"}))
X20X20=SUM(SUMIFS(Data!$J$4:$J$27,Data!$D$4:$D$27,$P$7,Data!$I$4:$I$27,">="&$N20,Data!$I$4:$I$27,"<="&$O20,Data!$G$4:$G$27,{"Amber","Green"},Data!$H$4:$H$27,{"Amber","Red"}))
P9:P22P9=$P$7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W8Cell Valuecontains "Lay"textNO
W8Cell Valuecontains "Back"textNO
W8Cell Valuecontains "Lay"textNO
W8Cell Valuecontains "Back"textNO
Cells with Data Validation
CellAllowCriteria
P7List=$F$2:$F$4
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Please also post some sample data from the Data sheet & an explanation of what you are trying to do.
 

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is the mini sheet not the sample data?

What im trying to do is

Get a value return for each row based on
League
Min Odds
Max Odds
Home Colour ( this is a combination of either one, two or all three colours )
Away Colour ( same as above )

so for example on one row i want the return from the database of

J league, Min Odds 1.20, Max Odds 1.30, Home colour Amber,Green, Away Colour Amber,Green,Red

Then this formula to be used on each row but with different combinations of the colours as shown in the cells
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Apologies, I hadn't realised that you had put everything on one sheet.
The formula in X20 is not working because you have a comma & not a semi-colon in the 2nd array.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,121
Messages
5,628,805
Members
416,342
Latest member
BlueDevil12

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