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!

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)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"LRBryan" wrote:

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!

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

"John C" wrote:

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)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"LRBryan" wrote:

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!

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
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, 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!



"John C" wrote:

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)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"LRBryan" wrote:

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!

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!

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
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!

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.
--
** John C **

"LRBryan" wrote:

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!



"John C" wrote:

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)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"LRBryan" wrote:

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!

LRBryan wrote:
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.

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
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!

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
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!

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

Le

"Glenn" wrote:

LRBryan wrote:
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.

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

"John C" wrote:

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.
--
** John C **

"LRBryan" wrote:

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!



"John C" wrote:

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)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"LRBryan" wrote:

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!

Yes, it does! My thanks for your persistence!

Le

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
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!

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
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!

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
Yes, it does! My thanks for your persistence!

Le

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
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!

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LRBryan" wrote in message
...
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!

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:G595999) * (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 !!!

--
** John C **

"LRBryan" wrote:

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

"John C" wrote:

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.
--
** John C **

"LRBryan" wrote:

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!



"John C" wrote:

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)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"LRBryan" wrote:

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!