Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : How to sum range ?
Hi,
I have the folowing table: A B C 1 Days Code Amount 2 1 K23 840.70 3 5 K23 840.70 4 14 P41 720.60 5 22 P41 111.00 6 120 G28 140.00 7 240 G24 720.60 8 360 L78 100.00 9 52 L78 100.00 10 68 K96 720.60 11 14 M24 100.00 12 450 C20 100.00 13 280 C20 100.00 14 68 K47 720.60 I wish to sum up amount in column C based the range of the days in column A as follows: 1. days is greater or equeal to 0 but less or equeal 250 2. days is greater or equeal to 251 but less or equeal 350 3. days is greater than 351 Can I use SUMIF formula or a Pivot Table or Advance Filter ? What are the 3 formulas ? Thanks Low -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : How to sum range ?
Using SUMIF the three formulas are as below...
=SUMIF(A:A,"<250",C:C) =SUMIF(A:A,"250",C:C)-SUMIF(A:A,"350",C:C) =SUMIF(A:A,"350",C:C) -- If this post helps click Yes --------------- Jacob Skaria "Mr. Low" wrote: Hi, I have the folowing table: A B C 1 Days Code Amount 2 1 K23 840.70 3 5 K23 840.70 4 14 P41 720.60 5 22 P41 111.00 6 120 G28 140.00 7 240 G24 720.60 8 360 L78 100.00 9 52 L78 100.00 10 68 K96 720.60 11 14 M24 100.00 12 450 C20 100.00 13 280 C20 100.00 14 68 K47 720.60 I wish to sum up amount in column C based the range of the days in column A as follows: 1. days is greater or equeal to 0 but less or equeal 250 2. days is greater or equeal to 251 but less or equeal 350 3. days is greater than 351 Can I use SUMIF formula or a Pivot Table or Advance Filter ? What are the 3 formulas ? Thanks Low -- A36B58K641 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : How to sum range ?
Hi,
In one column lets say from D1 to D3 emter the three options, then in cell E1 for the first option enter =sumproduct(--($A$1:$A$100<=250),$C$1:$C$100) in E2 enter =SUMPRODUCT(--($A$1:$A$100250),--($A$1:$A$100<=350),$c$1:$c$100) in E3 enter =sumproduct(--($A$1:$A$100350),$C$1:$C$100) if this helps please click yes, thankls "Mr. Low" wrote: Hi, I have the folowing table: A B C 1 Days Code Amount 2 1 K23 840.70 3 5 K23 840.70 4 14 P41 720.60 5 22 P41 111.00 6 120 G28 140.00 7 240 G24 720.60 8 360 L78 100.00 9 52 L78 100.00 10 68 K96 720.60 11 14 M24 100.00 12 450 C20 100.00 13 280 C20 100.00 14 68 K47 720.60 I wish to sum up amount in column C based the range of the days in column A as follows: 1. days is greater or equeal to 0 but less or equeal 250 2. days is greater or equeal to 251 but less or equeal 350 3. days is greater than 351 Can I use SUMIF formula or a Pivot Table or Advance Filter ? What are the 3 formulas ? Thanks Low -- A36B58K641 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : How to sum range ?
Hi Jacob,
Thanks for the formulas. Low -- A36B58K641 "Jacob Skaria" wrote: Using SUMIF the three formulas are as below... =SUMIF(A:A,"<250",C:C) =SUMIF(A:A,"250",C:C)-SUMIF(A:A,"350",C:C) =SUMIF(A:A,"350",C:C) -- If this post helps click Yes --------------- Jacob Skaria "Mr. Low" wrote: Hi, I have the folowing table: A B C 1 Days Code Amount 2 1 K23 840.70 3 5 K23 840.70 4 14 P41 720.60 5 22 P41 111.00 6 120 G28 140.00 7 240 G24 720.60 8 360 L78 100.00 9 52 L78 100.00 10 68 K96 720.60 11 14 M24 100.00 12 450 C20 100.00 13 280 C20 100.00 14 68 K47 720.60 I wish to sum up amount in column C based the range of the days in column A as follows: 1. days is greater or equeal to 0 but less or equeal 250 2. days is greater or equeal to 251 but less or equeal 350 3. days is greater than 351 Can I use SUMIF formula or a Pivot Table or Advance Filter ? What are the 3 formulas ? Thanks Low -- A36B58K641 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : How to sum range ?
Hi Eduardo,
Thanks for the formulas. Low -- A36B58K641 "Eduardo" wrote: Hi, In one column lets say from D1 to D3 emter the three options, then in cell E1 for the first option enter =sumproduct(--($A$1:$A$100<=250),$C$1:$C$100) in E2 enter =SUMPRODUCT(--($A$1:$A$100250),--($A$1:$A$100<=350),$c$1:$c$100) in E3 enter =sumproduct(--($A$1:$A$100350),$C$1:$C$100) if this helps please click yes, thankls "Mr. Low" wrote: Hi, I have the folowing table: A B C 1 Days Code Amount 2 1 K23 840.70 3 5 K23 840.70 4 14 P41 720.60 5 22 P41 111.00 6 120 G28 140.00 7 240 G24 720.60 8 360 L78 100.00 9 52 L78 100.00 10 68 K96 720.60 11 14 M24 100.00 12 450 C20 100.00 13 280 C20 100.00 14 68 K47 720.60 I wish to sum up amount in column C based the range of the days in column A as follows: 1. days is greater or equeal to 0 but less or equeal 250 2. days is greater or equeal to 251 but less or equeal 350 3. days is greater than 351 Can I use SUMIF formula or a Pivot Table or Advance Filter ? What are the 3 formulas ? Thanks Low -- A36B58K641 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : How to sum range ?
Your welcome
"Mr. Low" wrote: Hi Eduardo, Thanks for the formulas. Low -- A36B58K641 "Eduardo" wrote: Hi, In one column lets say from D1 to D3 emter the three options, then in cell E1 for the first option enter =sumproduct(--($A$1:$A$100<=250),$C$1:$C$100) in E2 enter =SUMPRODUCT(--($A$1:$A$100250),--($A$1:$A$100<=350),$c$1:$c$100) in E3 enter =sumproduct(--($A$1:$A$100350),$C$1:$C$100) if this helps please click yes, thankls "Mr. Low" wrote: Hi, I have the folowing table: A B C 1 Days Code Amount 2 1 K23 840.70 3 5 K23 840.70 4 14 P41 720.60 5 22 P41 111.00 6 120 G28 140.00 7 240 G24 720.60 8 360 L78 100.00 9 52 L78 100.00 10 68 K96 720.60 11 14 M24 100.00 12 450 C20 100.00 13 280 C20 100.00 14 68 K47 720.60 I wish to sum up amount in column C based the range of the days in column A as follows: 1. days is greater or equeal to 0 but less or equeal 250 2. days is greater or equeal to 251 but less or equeal 350 3. days is greater than 351 Can I use SUMIF formula or a Pivot Table or Advance Filter ? What are the 3 formulas ? Thanks Low -- A36B58K641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: How is the Excel 2007 file format open in Excel 2002 ? | Excel Discussion (Misc queries) | |||
Excel 2002 : Unable to open files in MS Outlook 2002 | Excel Discussion (Misc queries) | |||
Outlook 2002 calendar dates exported to Excel 2002 sort incorrectl | Excel Worksheet Functions | |||
How do I make range bar graphs in Excel 2002? | Charts and Charting in Excel | |||
Can you print labels using Excel 2002 in a Word 2002 mail merge? | Excel Discussion (Misc queries) |