Is there a "between" function?

[LRBryan]

Hi - I'm trying to use the "sumif" function, but have a large number of
variables to check for. What I'd like to do is:

=sumif(C8:C56,OR(between(5100,5999),5007,5008),E8:E56)

but this does not work (I'm assuming because the "between" function is not
valid.

Is there another way to do this?

Thanks!

 

[John C]

You could do something like this:
=SUMIF(C8:C56,">=5100",E8:E56)-SUMIF(C8:C56,">5999",E8:E56)+SUMIF(C8:C56,5007,E8:E56)+SUMIF(C8:C56,5008,E8:E56)

 

[LRBryan]

Thanks, John. I was hoping for something a little "smoother" but this may
work. I'll give it a try.

 

[Bernard Liengme]

let's do it in two stages
To get the ones for C between 5100 and 5999
=SUMPRODUCT( (C8:C14>=5100)*(C8:C14<=5999), E8:E14)
To get the ones for C = 5007 or 5008
=SUMPRODUCT( (C8:C14=5007)+(C8:C14=5008), E8:E14)
or
=SUMPRODUCT( (C8:C14={5007,5008})* E8:E14)

We can use both in
=SUMPRODUCT( (C8:C14>=5100)*(C8:C14<=5999), E8:E14) + SUMPRODUCT(
(C8:C14={5007,5008})* E8:E14)
or
=SUMPRODUCT( (C8:C14>=5100)*(C8:C14<=5999)+ (C8:C14={5007,5008})* E8:E14)


Note I used a smaller test range: change 14 to 56 everywhere
best wishes

 

[LRBryan]

John, unfortunately, this only works if the account in column C8:C56 is
either 5007 or 5008. It's not evaluating the accounts greater than 5100 and
less than 5999. The accounts were brought in as text...I may be able to go
back and format as number, but am not sure if it will work even then. Will
have to give it a try.

Thanks!

 

[LRBryan]

Bernard, I did give this a try but am thinking this is not really doing what
I need it to do because I'm not trying to sum products.

To explain, I have a number of lines containing account codes (col C6:C58)
and budgeted amounts (col E6:E58).

In a single cell, I need to sum the budgeted amounts of all accounts that
have codes that fall between 5100 and 5999, OR if they are accounts 5007 or
5008.

Thanks!

 

[John C]

Change the first part of the formula as follows:
SUMPRODUCT((--(C8:C56)>=5100)*(E8:E56))-SUMPRODUCT((--(C8:C56)>5999)*(E8:E56))
The double unary (-- in this case), ensures that the C8:C56 range will be
treated as a number, and then the math will workout. If you are still having
difficult, you may want to add the TRIM function (just type TRIM right after
the second dash in the 2 double unaries.

 

[Glenn]

Bernard, I did give this a try but am thinking this is not really doing what
I need it to do because I'm not trying to sum products.

To explain, I have a number of lines containing account codes (col C6:C58)
and budgeted amounts (col E6:E58).

In a single cell, I need to sum the budgeted amounts of all accounts that
have codes that fall between 5100 and 5999, OR if they are accounts 5007 or
5008.

Thanks!

Are you saying you tried it and you got the wrong answer, or that you think it
won't work because you believe the wrong function was suggested. SUMPRODUCT is
the correct function for your problem.

 

[Bernard Liengme]

As Glen points out the name SUMPRODUCT is misleading you.
We are multiplying the values by 1 if we want them and by 0 if we do not
want them
Try the formal on some simple data; It does work, I tried it!
best wishes

 

[LRBryan]

Yes, you're correct. I did get the "sumproduct" to work, though I don't know
how it works!

Le

 

[LRBryan]

John,

I have absolutely NO idea why this works, but it does! My thanks for your
assistance. What I wound up using was
=SUMPRODUCT((--(G6:G59)>5100)*(O6:O59))-SUMPRODUCT((--(G6:G59)>5999)*(O6:O59))+SUMPRODUCT((--(G6:G59)=5007)*(O6:O59))+SUMPRODUCT((--(G6:G59)=5008)*(O6:O59))

I do not understand the logic here, but this is the first time I've seen
"sumproduct". Just not sure I could explain it to anyone else!!
Le

 

[LRBryan]

Yes, it does! My thanks for your persistence!

Le

 

[Bernard Liengme]

Have a look here to learn more
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Persistence!! I was a university prof for 40 years!!
all the best

 

[John C]

First thing first, you need to change your >5100 to >=5100 or it will exclude
any account 5100. I'll give you the details here of how it works, as
SUMPRODUCT is a very powerful function that, once learned, will do so much
for you.
Let's give a sample data set (and I will assume the >=5100 correction), and
I'll break down your formula to show why it works.
We'll use a smaller set of data, say row 6-12
G6:G12 = 5007 | 6005 | 5009 | 5822 | 5100 | 5008 | 6000
O6:O12 = 240 | 125 | 415 | 118 | 644 | 557 | 99
Now, just looking at the data, we know the answer should be 240 + 118 + 644
+ 557 = 1559
Let's look at the first SUMPRODUCT
(G6:G59>=5100) this will evaluate into a series of TRUE or FALSE results,
again, as stated, only looking at first 7, so...
(G6:G59>=5100) = FALSE | TRUE | FALSE | TRUE | TRUE | FALSE | TRUE ...
The double dash (double unary negation), essentially turns a text value into
a number if possible. The great thing is, TRUE and FALSE can be turned into
numbers, such as 1 and 0 in this case.
So...
--(G6:G59>=5100) = 0 | 1 | 0 | 1 | 1 | 0 | 1 ...
Then multiplies this by the values in O6:O59
So...
--(G6:G59>=5100) * (O6:O59) = 0 * 240 + 1 * 125 + 0 * 415 + 1 * 118 + 1 *
644 + 0 * 557 + 1 * 99 = 986
And the next 3 terms can be solved just the same way
So...
--(G6:G59>5999) * (O6:O59) = 0 * 240 + 1 * 125 + 0 * 415 + 0 * 118 + 0 * 644
+ 0 * 557 + 1 * 99 = 224
--(G6:G59=5007) * (O6:O59) = 1 * 240 + 0 * 125 + 0 * 415 + 0 * 118 + 0 * 644
+ 0 * 557 + 0 * 99 = 240
--(G6:G59=5008) * (O6:O59) = 0 * 240 + 0 * 125 + 0 * 415 + 0 * 118 + 0 * 644
+ 1 * 557 + 0 * 99 = 557
And finally, we solve based on the four terms
986 - 224 + 240 + 557 = 1559

Hope that clears things up, and don't forget to change the >5100 to >=5100 !!!