SUM & SUMIFS formula not returning correct value

J

juca73

New Member
Joined
Dec 30, 2017
Messages
40
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
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
What is the formula in the cell that is correct?
 
Upvote 0
=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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Please also post some sample data from the Data sheet & an explanation of what you are trying to do.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
61
Cubist
C
alketrazz
  • Solved
SUMIFS, with specific value or ALL entries
Replies
3
Views
193
Fluff
Fluff
J
  • Solved
help finding my error
Replies
1
Views
145
Proulxs
P
E
  • Solved
Summing Values
Replies
3
Views
211
LazyBug
LazyBug
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
134
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,822
Messages
6,121,765
Members
449,049
Latest member
greyangel23

Share this page

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
Back
Top