SUMIF Formula including OR

G

garymu16

Board Regular
Joined
Sep 15, 2006
Messages
139
Dear All,

How do I modify my formula (below) to include an OR statement?

=SUMIF(I4:I20,"Core",G4:G20)

I need something like this:

=SUMIF(I4:I20,"Core" or "Existing",G4:G20)

I know this will no-doubt be embarassingly easy but I can't get my head around it.

Thx in advance
Gary
 
Sort by date Sort by votes
If the criteria is an array constant as in:

=SUM(SUMIF(I4:I20,{"Core","Existing"},G4:G20))

the Sum call would be cheaper than the SumProduct call as in:

=SUMPRODUCT(SUMIF(I4:I20,{"Core","Existing"},G4:G20))

If the criteria/conditions are in a list (a range)...

=SUM(SUMIF(I4:I20,List,G4:G20))

which will require applying control+shift+enter, would be as good as:

=SUMPRODUCT(SUMIF(I4:I20,List,G4:G20))

You can of course "substitute" IsNumber/Match for SumIf in all these formulas
combined with Sum or SumProduct...

=SUMPRODUCT(--ISNUMBER(MATCH(I4:I20,List,0)),G4:G20)

BTW, this is not slower (rather other way around) than the + form of Or...
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Thanks Aladin. :)

Of the two easily exendable formulas (for more conditions) which do you prefer?
Code: 
=SUM(SUMIF(I4:I20,{"Core","Existing"},G4:G20))
=SUMPRODUCT(--ISNUMBER(MATCH(I4:I20,{"Core","Existing"},0)),G4:G20)

Or is that purely a stylistic choice?



=SUMPRODUCT(--ISNUMBER(MATCH(I4:I20,List,0)),G4:G20)
BTW, this is not slower (rather other way around) than the + form of Or...
Click to expand...
Thanks for mentioning that - I will have to revisit it, I thought the "+ form" of OR was faster.

Cheers,
Colin
 
Upvote 0
Colin_L said:
Thanks Aladin. :)

Of the two easily exendable formulas (for more conditions) which do you prefer?
Code: 
=SUM(SUMIF(I4:I20,{"Core","Existing"},G4:G20))
=SUMPRODUCT(--ISNUMBER(MATCH(I4:I20,{"Core","Existing"},0)),G4:G20)

Or is that purely a stylistic choice?
Click to expand...

SUM will/should be faster than SUMPRODUCT with array-constants... But with variablization a SumProduct formula is more convenient.

I did not carry out any timing regarding SumIf vs IsNumber/Match...

Thanks for mentioning that - I will have to revisit it, I thought the "+ form" of OR was faster.

Cheers,
Colin
Click to expand...

If you have time to bechmark them, we would be able to go beyond a mere hunch...
 
Upvote 0
If you have time to bechmark them, we would be able to go beyond a mere hunch...
Click to expand...

I did some light speed testing with the VBA timer:

These two were equally the fastest with no difference between them:
Code: 
1 =SUMIF(I4:I1000,"Core",G4:G1000)+SUMIF(I4:I1000,"Existing",G4:G1000)
2 =SUMIF(I4:I1000,"Core",G4)+SUMIF(I4:I1000,"Existing",G4)

These three were just a tiny fraction slower, all equal accounting for background noise:
Code: 
3 =SUM(SUMIF(I4:I1000,{"Core","Existing"},G4:G1000))
4 =SUM(SUMIF(I4:I1000,{"Core","Existing"},G4))
5 =SUMPRODUCT(SUMIF(I4:I1000,{"Core","Existing"},G4:G1000))

These two were a lot slower, and the "+" version was slightly the slower of these two ;)
Code: 
6 =SUMPRODUCT(--ISNUMBER(MATCH(I4:I1000,{"Core","Existing"},0)),G4:G1000)
7 =SUMPRODUCT((I4:I1000="Core")+(I4:I1000="Existing"),G4:G1000)
Apologies to Barry, my mistake on these last two.

Given the overhead with the last two compared to the others, if we're using array constants as a list, I'd say that either 3 or 4 is the way to go if you have a long list. Given that the efficiency overhead is so minimal, I think I'd opt for 3 or 4 even if there are only 2 conditions because the formula is slightly more concise, but it's really horses for courses.

Colin
 
Last edited:
Upvote 0
Colin_L said:
I did some light speed testing with the VBA timer:

These two were equally the fastest with no difference between them:
Code: 
1 =SUMIF(I4:I1000,"Core",G4:G1000)+SUMIF(I4:I1000,"Existing",G4:G1000)
2 =SUMIF(I4:I1000,"Core",G4)+SUMIF(I4:I1000,"Existing",G4)

These three were just a tiny fraction slower, all equal accounting for background noise:
Code: 
3 =SUM(SUMIF(I4:I1000,{"Core","Existing"},G4:G1000))
4 =SUM(SUMIF(I4:I1000,{"Core","Existing"},G4))
5 =SUMPRODUCT(SUMIF(I4:I1000,{"Core","Existing"},G4:G1000))

These two were a lot slower, and the "+" version was slightly the slower of these two ;)
Code: 
6 =SUMPRODUCT(--ISNUMBER(MATCH(I4:I1000,{"Core","Existing"},0)),G4:G1000)
7 =SUMPRODUCT((I4:I1000="Core")+(I4:I1000="Existing"),G4:G1000)
Apologies to Barry, my mistake on these last two.

Given the overhead with the last two compared to the others, if we're using array constants as a list, I'd say that either 3 or 4 is the way to go if you have a long list. Given that the efficiency overhead is so minimal, I think I'd opt for 3 or 4 even if there are only 2 conditions because the formula is slightly more concise, but it's really horses for courses.

Colin
Click to expand...

Getting greedy here... How about:

{=SUM(SUMIF(I4:I1000,List,G4:G1000))}

vs

=SUMPRODUCT(SUMIF(I4:I1000,List,G4:G1000))

vs

=SUMPRODUCT(--ISNUMBER(MATCH(I4:I1000,List,0)),G4:G1000)

Make List have 5 items or so.
 
Upvote 0
I take it you want 'List' not to be an array constant on these three?

I'll test them tomorrow. :)
 
Upvote 0
Sorry for the slow reply....

Tested in Excel 2007
Where data is stored as follows:
Code: 
Data!A2:A30000 houses the text "Word1";"Word2" etc... through to "Word16" but excludes "Word5"
Data!B2:B30000 houses a mixture of positive and negative numbers
Data!D2:D6 houses the list {"Word1";"Word2";"Word3";"Word4";"Word5"}
Where the following names are set up:
Code: 
List_Array ={"Word1";"Word2";"Word3";"Word4";"Word5"}
List_Range =Data!$D$2:$D$6
These are the formulas timed:
Code: 
1  =SUMIF($A$2:$A$30000,"Word1",$B$2:$B$30000)+SUMIF($A$2:$A$30000,"Word2",$B$2:$B$30000)+SUMIF($A$2:$A$30000,"Word3",$B$2:$B$30000)+SUMIF($A$2:$A$30000,"Word4",$B$2:$B$30000)+SUMIF($A$2:$A$30000,"Word5",$B$2:$B$30000)
2  =SUMIF($A$2:$A$30000,"Word1",$B$2)+SUMIF($A$2:$A$30000,"Word2",$B$2)+SUMIF($A$2:$A$30000,"Word3",$B$2)+SUMIF($A$2:$A$30000,"Word4",$B$2)+SUMIF($A$2:$A$30000,"Word5",$B$2)
3  =SUM(SUMIF($A$2:$A$30000,{"Word1";"Word2";"Word3";"Word4";"Word5"},$B$2:$B$30000))
4  =SUM(SUMIF($A$2:$A$30000,{"Word1";"Word2";"Word3";"Word4";"Word5"},$B$2))
5  =SUM(SUMIFS($B$2:$B$30000,$A$2:$A$30000,{"Word1";"Word2";"Word3";"Word4";"Word5"}))
6  =SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$30000,{"Word1";"Word2";"Word3";"Word4";"Word5"},0)),$B$2:$B$30000)
7  =-SUMPRODUCT(-ISNUMBER(MATCH($A$2:$A$30000,{"Word1";"Word2";"Word3";"Word4";"Word5"},0)),$B$2:$B$30000)
8  =SUMPRODUCT(($A$2:$A$30000="Word1")+($A$2:$A$30000="Word2")+($A$2:$A$30000="Word3")+($A$2:$A$30000="Word4")+($A$2:$A$30000="Word5"),$B$2:$B$30000)
9  {=SUM(SUMIF($A$2:$A$30000,List_Range,$B$2:$B$30000))}
10 =SUMPRODUCT(SUMIF($A$2:$A$30000,List_Range,$B$2:$B$30000))
11 =SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$30000,List_Range,0)),$B$2:$B$30000)
12 =-SUMPRODUCT(-ISNUMBER(MATCH($A$2:$A$30000,List_Range,0)),$B$2:$B$30000)
13 {=SUM(SUMIF($A$2:$A$30000,List_Array,$B$2:$B$30000))}
14 =SUMPRODUCT(SUMIF($A$2:$A$30000,List_Array,$B$2:$B$30000))
15 =SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$30000,List_Array,0)),$B$2:$B$30000)
These are the results I got over 200 timings (with the VBA timer) of each (in seconds):

<TABLE style="WIDTH: 417pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=556 border=0><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 68pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=91 height=17>Formula</TD><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 80pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=106>Slowest</TD><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 77pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=103>Fastest</TD><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 94pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=125>Mean Average</TD><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 98pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=131>Standard Deviation</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.890625</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.828125</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.849609375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.008498826</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.828125</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.849375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.008294639</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.84375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.8509375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.008412133</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>4</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.84375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.850234375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.008329693</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.84375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.851484375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.008435071</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>6</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.546875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.1875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.22671875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.028882951</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>7</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.5625</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.125</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.170703125</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.034777985</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>8</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">4.09375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">3.9375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">3.988984375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.032017794</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>9</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.90625</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.84375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.8528125</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.009314609</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>10</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.890625</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.828125</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.8509375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.009112209</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>11</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.75</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.4375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.47765625</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.028606381</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.5</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.390625</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.422421875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.020744139</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>13</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.921875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.84375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.85203125</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.009771002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>14</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.84375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.850390625</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.008205041</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.265625</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.1875</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">2.224375</TD><TD class=xl63 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">0.018668689</TD></TR></TBODY></TABLE>


Was 10 vs 11 what you expected? Quite eye opening, I thought.

Colin
 
Upvote 0
Ok so I won't suggest the SUMPRODUCT(()+()) method again. :biggrin:

Amazing at how vast the time differences are! Interesting stuff, although I doubt I'll ever really understand why?!
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Multiple Sumif across columns
Replies
4
Views
642
Fluff
Fluff
Y
  • Question Question
While 64 bit excel uses 12 cores at very high values such as 96%-99%, it uses only 1.5 GB of 16 GB RAM.
Replies
2
Views
383
ysfuyar
Y
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
210
Alex Blakenburg
A
8
  • Solved
How to avoid spill error on Sumif
Replies
4
Views
222
Eric W
Eric W
J
  • Question Question
sumif function with delayed application
Replies
5
Views
308
jbrown021286
J

Forum statistics

Threads
1,222,647
Messages
6,167,324
Members
452,110
Latest member
eui

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