Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a "between" function?
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |