Home |
Search |
Today's Posts |
#1
|
|||
|
|||
=SUMIF(A:A,111,B:B)
or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#2
|
|||
|
|||
SUMIF Question
This may or may not be possible...... But I would like to be able to sum up
the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#3
|
|||
|
|||
What if I want to make the criteria a range... such as if I want to sum up
all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#4
|
|||
|
|||
Try either of these:
=SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#5
|
|||
|
|||
I appreciate the suggestions but.... these response seem to multiply and I
need to add them.... I guess my basic question is. Is there any way to a have a RANGE in the Criteria portion of the SUMIF formula..... the range is hundreds of numbers and would be impractical to list every one of them separated by commas.... Any and all suggestions would be appreciated greatly Steve "Ragdyer" wrote: Try either of these: =SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#6
|
|||
|
|||
Neither of my suggestions will *multiply* any values.
Is there any particular reason why you chose not to at *least try* the suggestions before rejecting them? If you have "hundreds" of numbers, the SumProduct formula would probably be the way to go. I would suggest for the *second* time (first was in my first post), to *try* the suggestions. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steve" wrote in message ... I appreciate the suggestions but.... these response seem to multiply and I need to add them.... I guess my basic question is. Is there any way to a have a RANGE in the Criteria portion of the SUMIF formula..... the range is hundreds of numbers and would be impractical to list every one of them separated by commas.... Any and all suggestions would be appreciated greatly Steve "Ragdyer" wrote: Try either of these: =SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#7
|
|||
|
|||
RD,
I did try them and verbatim.... I got Value errors so I assumed that I had miscommunicated my intent and that for whatever reason that it wouldn't work. I am also rather new to the full capabilities of Excel and when I see 2 sets of parentheses next to each other I assumed they would be multiplied. I will give them another try and I appreciate your input Thanks, Steve "Ragdyer" wrote: Neither of my suggestions will *multiply* any values. Is there any particular reason why you chose not to at *least try* the suggestions before rejecting them? If you have "hundreds" of numbers, the SumProduct formula would probably be the way to go. I would suggest for the *second* time (first was in my first post), to *try* the suggestions. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steve" wrote in message ... I appreciate the suggestions but.... these response seem to multiply and I need to add them.... I guess my basic question is. Is there any way to a have a RANGE in the Criteria portion of the SUMIF formula..... the range is hundreds of numbers and would be impractical to list every one of them separated by commas.... Any and all suggestions would be appreciated greatly Steve "Ragdyer" wrote: Try either of these: =SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#8
|
|||
|
|||
We can now talk about the different forms of the SumProduct() function.
You'll get a #VALUE! error with the asterisk form (what I suggested), if *any* of the values in (Column C), are *not* numeric. You might consider this something "good" or something "bad", "Good", in that you now know that you have contaminated, or in the least, "different" data. "Bad" in that you don't have a viable return. You might try the unary (comma) version, which will bypass the "bad" (different) data, and give you a return, HOWEVER, without informing you of the presence of this "different" data in your list, and therefore *perhaps* returning *incorrect* solutions. =SUMPRODUCT(--(B4:B40=111),--(B4:B40<=120),C4:C40) It's your choice as to whether or not you need or want to know of the presence of this "different " data. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Steve" wrote in message ... RD, I did try them and verbatim.... I got Value errors so I assumed that I had miscommunicated my intent and that for whatever reason that it wouldn't work. I am also rather new to the full capabilities of Excel and when I see 2 sets of parentheses next to each other I assumed they would be multiplied. I will give them another try and I appreciate your input Thanks, Steve "Ragdyer" wrote: Neither of my suggestions will *multiply* any values. Is there any particular reason why you chose not to at *least try* the suggestions before rejecting them? If you have "hundreds" of numbers, the SumProduct formula would probably be the way to go. I would suggest for the *second* time (first was in my first post), to *try* the suggestions. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Steve" wrote in message ... I appreciate the suggestions but.... these response seem to multiply and I need to add them.... I guess my basic question is. Is there any way to a have a RANGE in the Criteria portion of the SUMIF formula..... the range is hundreds of numbers and would be impractical to list every one of them separated by commas.... Any and all suggestions would be appreciated greatly Steve "Ragdyer" wrote: Try either of these: =SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#9
|
|||
|
|||
I forgot to mention that *both* forms will simply bypass any cells with
non-numeric data in Column B. This also leaves you open to incorrect returns, but without any means of notification. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "RagDyeR" wrote in message ... We can now talk about the different forms of the SumProduct() function. You'll get a #VALUE! error with the asterisk form (what I suggested), if *any* of the values in (Column C), are *not* numeric. You might consider this something "good" or something "bad", "Good", in that you now know that you have contaminated, or in the least, "different" data. "Bad" in that you don't have a viable return. You might try the unary (comma) version, which will bypass the "bad" (different) data, and give you a return, HOWEVER, without informing you of the presence of this "different" data in your list, and therefore *perhaps* returning *incorrect* solutions. =SUMPRODUCT(--(B4:B40=111),--(B4:B40<=120),C4:C40) It's your choice as to whether or not you need or want to know of the presence of this "different " data. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Steve" wrote in message ... RD, I did try them and verbatim.... I got Value errors so I assumed that I had miscommunicated my intent and that for whatever reason that it wouldn't work. I am also rather new to the full capabilities of Excel and when I see 2 sets of parentheses next to each other I assumed they would be multiplied. I will give them another try and I appreciate your input Thanks, Steve "Ragdyer" wrote: Neither of my suggestions will *multiply* any values. Is there any particular reason why you chose not to at *least try* the suggestions before rejecting them? If you have "hundreds" of numbers, the SumProduct formula would probably be the way to go. I would suggest for the *second* time (first was in my first post), to *try* the suggestions. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Steve" wrote in message ... I appreciate the suggestions but.... these response seem to multiply and I need to add them.... I guess my basic question is. Is there any way to a have a RANGE in the Criteria portion of the SUMIF formula..... the range is hundreds of numbers and would be impractical to list every one of them separated by commas.... Any and all suggestions would be appreciated greatly Steve "Ragdyer" wrote: Try either of these: =SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#10
|
|||
|
|||
RD,
I got it to work.....as it is 99% of the time....operator error...... I used the columns from my example instead of the columns that had the actual data... stupid mistake but that is how it sometimes is. I really appreciated your input. Incidently, what does the "*" do in this formula, what does a "$" surrounding cell references do and when I see "--" in formulas.... do you actually type those in or does it mean something else.....? If you have the time or inclination to explain that I would really appreciate that as well. You have been a great help. Steve "RagDyeR" wrote: I forgot to mention that *both* forms will simply bypass any cells with non-numeric data in Column B. This also leaves you open to incorrect returns, but without any means of notification. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "RagDyeR" wrote in message ... We can now talk about the different forms of the SumProduct() function. You'll get a #VALUE! error with the asterisk form (what I suggested), if *any* of the values in (Column C), are *not* numeric. You might consider this something "good" or something "bad", "Good", in that you now know that you have contaminated, or in the least, "different" data. "Bad" in that you don't have a viable return. You might try the unary (comma) version, which will bypass the "bad" (different) data, and give you a return, HOWEVER, without informing you of the presence of this "different" data in your list, and therefore *perhaps* returning *incorrect* solutions. =SUMPRODUCT(--(B4:B40=111),--(B4:B40<=120),C4:C40) It's your choice as to whether or not you need or want to know of the presence of this "different " data. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Steve" wrote in message ... RD, I did try them and verbatim.... I got Value errors so I assumed that I had miscommunicated my intent and that for whatever reason that it wouldn't work. I am also rather new to the full capabilities of Excel and when I see 2 sets of parentheses next to each other I assumed they would be multiplied. I will give them another try and I appreciate your input Thanks, Steve "Ragdyer" wrote: Neither of my suggestions will *multiply* any values. Is there any particular reason why you chose not to at *least try* the suggestions before rejecting them? If you have "hundreds" of numbers, the SumProduct formula would probably be the way to go. I would suggest for the *second* time (first was in my first post), to *try* the suggestions. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Steve" wrote in message ... I appreciate the suggestions but.... these response seem to multiply and I need to add them.... I guess my basic question is. Is there any way to a have a RANGE in the Criteria portion of the SUMIF formula..... the range is hundreds of numbers and would be impractical to list every one of them separated by commas.... Any and all suggestions would be appreciated greatly Steve "Ragdyer" wrote: Try either of these: =SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#11
|
|||
|
|||
Thanks for the feed-back, and here's a couple of links that should answer
your questions: http://www.mcgimpsey.com/excel/formulae/doubleneg.html http://www.xldynamic.com/source/xld.SUMPRODUCT.html Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steve" wrote in message ... RD, I got it to work.....as it is 99% of the time....operator error...... I used the columns from my example instead of the columns that had the actual data... stupid mistake but that is how it sometimes is. I really appreciated your input. Incidently, what does the "*" do in this formula, what does a "$" surrounding cell references do and when I see "--" in formulas.... do you actually type those in or does it mean something else.....? If you have the time or inclination to explain that I would really appreciate that as well. You have been a great help. Steve "RagDyeR" wrote: I forgot to mention that *both* forms will simply bypass any cells with non-numeric data in Column B. This also leaves you open to incorrect returns, but without any means of notification. -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "RagDyeR" wrote in message ... We can now talk about the different forms of the SumProduct() function. You'll get a #VALUE! error with the asterisk form (what I suggested), if *any* of the values in (Column C), are *not* numeric. You might consider this something "good" or something "bad", "Good", in that you now know that you have contaminated, or in the least, "different" data. "Bad" in that you don't have a viable return. You might try the unary (comma) version, which will bypass the "bad" (different) data, and give you a return, HOWEVER, without informing you of the presence of this "different" data in your list, and therefore *perhaps* returning *incorrect* solutions. =SUMPRODUCT(--(B4:B40=111),--(B4:B40<=120),C4:C40) It's your choice as to whether or not you need or want to know of the presence of this "different " data. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Steve" wrote in message ... RD, I did try them and verbatim.... I got Value errors so I assumed that I had miscommunicated my intent and that for whatever reason that it wouldn't work. I am also rather new to the full capabilities of Excel and when I see 2 sets of parentheses next to each other I assumed they would be multiplied. I will give them another try and I appreciate your input Thanks, Steve "Ragdyer" wrote: Neither of my suggestions will *multiply* any values. Is there any particular reason why you chose not to at *least try* the suggestions before rejecting them? If you have "hundreds" of numbers, the SumProduct formula would probably be the way to go. I would suggest for the *second* time (first was in my first post), to *try* the suggestions. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Steve" wrote in message ... I appreciate the suggestions but.... these response seem to multiply and I need to add them.... I guess my basic question is. Is there any way to a have a RANGE in the Criteria portion of the SUMIF formula..... the range is hundreds of numbers and would be impractical to list every one of them separated by commas.... Any and all suggestions would be appreciated greatly Steve "Ragdyer" wrote: Try either of these: =SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#12
|
|||
|
|||
Again, RD, Thanks!!
"RagDyer" wrote: Thanks for the feed-back, and here's a couple of links that should answer your questions: http://www.mcgimpsey.com/excel/formulae/doubleneg.html http://www.xldynamic.com/source/xld.SUMPRODUCT.html Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steve" wrote in message ... RD, I got it to work.....as it is 99% of the time....operator error...... I used the columns from my example instead of the columns that had the actual data... stupid mistake but that is how it sometimes is. I really appreciated your input. Incidently, what does the "*" do in this formula, what does a "$" surrounding cell references do and when I see "--" in formulas.... do you actually type those in or does it mean something else.....? If you have the time or inclination to explain that I would really appreciate that as well. You have been a great help. Steve "RagDyeR" wrote: I forgot to mention that *both* forms will simply bypass any cells with non-numeric data in Column B. This also leaves you open to incorrect returns, but without any means of notification. -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "RagDyeR" wrote in message ... We can now talk about the different forms of the SumProduct() function. You'll get a #VALUE! error with the asterisk form (what I suggested), if *any* of the values in (Column C), are *not* numeric. You might consider this something "good" or something "bad", "Good", in that you now know that you have contaminated, or in the least, "different" data. "Bad" in that you don't have a viable return. You might try the unary (comma) version, which will bypass the "bad" (different) data, and give you a return, HOWEVER, without informing you of the presence of this "different" data in your list, and therefore *perhaps* returning *incorrect* solutions. =SUMPRODUCT(--(B4:B40=111),--(B4:B40<=120),C4:C40) It's your choice as to whether or not you need or want to know of the presence of this "different " data. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Steve" wrote in message ... RD, I did try them and verbatim.... I got Value errors so I assumed that I had miscommunicated my intent and that for whatever reason that it wouldn't work. I am also rather new to the full capabilities of Excel and when I see 2 sets of parentheses next to each other I assumed they would be multiplied. I will give them another try and I appreciate your input Thanks, Steve "Ragdyer" wrote: Neither of my suggestions will *multiply* any values. Is there any particular reason why you chose not to at *least try* the suggestions before rejecting them? If you have "hundreds" of numbers, the SumProduct formula would probably be the way to go. I would suggest for the *second* time (first was in my first post), to *try* the suggestions. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Steve" wrote in message ... I appreciate the suggestions but.... these response seem to multiply and I need to add them.... I guess my basic question is. Is there any way to a have a RANGE in the Criteria portion of the SUMIF formula..... the range is hundreds of numbers and would be impractical to list every one of them separated by commas.... Any and all suggestions would be appreciated greatly Steve "Ragdyer" wrote: Try either of these: =SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40)) OR =SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "Steve" wrote in message ... What if I want to make the criteria a range... such as if I want to sum up all the dollar amounts for a Expense codes from 111 -120 to include 111,112,113,114, etc. I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even though there was a number in that range.... Did I screw it up some how? Thanks "CLR" wrote: =SUMIF(A:A,111,B:B) or, put the "111" in C1 and use =SUMIF(A:A,C1,B:B) Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... This may or may not be possible...... But I would like to be able to sum up the numbers from one column only if they are For example A B Expense Code Budgeted Amt. 111 $100 112 $200 111 $300 I would like a formula that add up only the dollar amounts for items whose "Expense Code" is 111....... There are forty line items with various expense codes and I need to be able to total everything as well as break it down by expense code for a separate table. I can't designate certain cells to be included because they are constantly changing or are different for each case (110 sheets represent 110 different entities) so the formula must have the criteria from one cell (Expense Code) to filter outthe dollar amounts. Is this even possible? Any and all help will be greatly appreciated Steve |
#13
|
|||
|
|||
That is amazing, RD, that is *exactly* what I was looking for. I was getting pretty frustrated over SUMIF not accepting multiple arguments, even with AND and OR functions. Your SUMPRODUCT solution worked flawlessly for what I needed to accomplish. Great help, and good links, too. Thanks. +500 :) -- centerNegative ------------------------------------------------------------------------ centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921 View this thread: http://www.excelforum.com/showthread...hreadid=465776 |
#14
|
|||
|
|||
You're welcome, and appreciate the feed-back.
This is exactly what these groups are good for, and what they're supposed to do ... helping others *in addition* to the OP. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "centerNegative" <centerNegative.1vmmed_1127167509.0258@excelforu m-nospam.com wrote in message news:centerNegative.1vmmed_1127167509.0258@excelfo rum-nospam.com... That is amazing, RD, that is *exactly* what I was looking for. I was getting pretty frustrated over SUMIF not accepting multiple arguments, even with AND and OR functions. Your SUMPRODUCT solution worked flawlessly for what I needed to accomplish. Great help, and good links, too. Thanks. +500 :) -- centerNegative ------------------------------------------------------------------------ centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921 View this thread: http://www.excelforum.com/showthread...hreadid=465776 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
this may be simple, sumif question | Excel Worksheet Functions | |||
SumIF function | Excel Discussion (Misc queries) | |||
sumif Question | Excel Discussion (Misc queries) | |||
SUMIF question | Excel Worksheet Functions |