Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers in column b if date is between certain criteria
I want to add a column of numbers (column AL, which contains #.# Grams) if
column C (dates) is between a range of dates (ie. between 1/1/06 and 3/31/06). I could use sumproduct if it was text, to count the number of occurrances of a certain text, but how to I get it to add numbers....? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers in column b if date is between certain criteria
Try this:
=SUMPRODUCT((C2:C1000=DATE(2006,1,1))*(C2:C1000<= DATE(2006,3,31))*AL2:AL1000) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jeremy Ellison" wrote in message ... I want to add a column of numbers (column AL, which contains #.# Grams) if column C (dates) is between a range of dates (ie. between 1/1/06 and 3/31/06). I could use sumproduct if it was text, to count the number of occurrances of a certain text, but how to I get it to add numbers....? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers in column b if date is between certain criteria
You could try:
=SUMIF($C$2:$C$21,"<=03/31/06",$AL$2:$AL$21)-SUMIF($C$2:$C$21,"<01/01/06",$AL$2:$AL$21) Format as general. Hope this helps Rowan Jeremy Ellison wrote: I want to add a column of numbers (column AL, which contains #.# Grams) if column C (dates) is between a range of dates (ie. between 1/1/06 and 3/31/06). I could use sumproduct if it was text, to count the number of occurrances of a certain text, but how to I get it to add numbers....? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers in column b if date is between certain criteria
Very helpful, from both of you.... I think that would work. I forgot to
mention that I also need to select which numbers to add within the column.... C AT AU 1 Date DRUG Amount 2 1/1/6 Heroin 2.2 Grams 3 1/2/7 Heroin 5.5 Grams I need it to add just the "heroin" -- or what ever other drug I want to add up, recovered between 1/1/6 adn 3/31/6.... teh result of this formula from above should then be 2.2 grams... Thank you again for your help...I am learning lots from reading how u put this stuff into action! "Rowan Drummond" wrote: You could try: =SUMIF($C$2:$C$21,"<=03/31/06",$AL$2:$AL$21)-SUMIF($C$2:$C$21,"<01/01/06",$AL$2:$AL$21) Format as general. Hope this helps Rowan Jeremy Ellison wrote: I want to add a column of numbers (column AL, which contains #.# Grams) if column C (dates) is between a range of dates (ie. between 1/1/06 and 3/31/06). I could use sumproduct if it was text, to count the number of occurrances of a certain text, but how to I get it to add numbers....? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers in column b if date is between certain criteria
Just add another argument to the original formula:
Watch out though ... you're switching amount columns in your questions! =SUMPRODUCT((C2:C1000=DATE(2006,1,1))*(C2:C1000<= DATE(2006,3,31))*(AT2:AT10 00="Heroin")*AL2:AL1000) I would suggest that you consider using specific cells to designate your variables, so that you won't have to revise the formula itself, if and when you change time periods and drugs. A1 = start date A2 = end date A3 = drug =SUMPRODUCT((C2:C1000=A1)*(C2:C1000<=A2)*(AT2:AT1 000=A3)*AL2:AL1000) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Jeremy Ellison" wrote in message ... Very helpful, from both of you.... I think that would work. I forgot to mention that I also need to select which numbers to add within the column.... C AT AU 1 Date DRUG Amount 2 1/1/6 Heroin 2.2 Grams 3 1/2/7 Heroin 5.5 Grams I need it to add just the "heroin" -- or what ever other drug I want to add up, recovered between 1/1/6 adn 3/31/6.... teh result of this formula from above should then be 2.2 grams... Thank you again for your help...I am learning lots from reading how u put this stuff into action! "Rowan Drummond" wrote: You could try: =SUMIF($C$2:$C$21,"<=03/31/06",$AL$2:$AL$21)-SUMIF($C$2:$C$21,"<01/01/06",$A L$2:$AL$21) Format as general. Hope this helps Rowan Jeremy Ellison wrote: I want to add a column of numbers (column AL, which contains #.# Grams) if column C (dates) is between a range of dates (ie. between 1/1/06 and 3/31/06). I could use sumproduct if it was text, to count the number of occurrances of a certain text, but how to I get it to add numbers....? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers in column b if date is between certain criteria
=SUMPRODUCT((CaseData!C2:C1000=DATE(2005,10,1))*( CaseData!C2:C1000<=DATE(200,12,31))*(CaseData!AT2: AT1000="Heroin")*CaseData!AU2:AU1000)
is what I put in there.... I got back a "VALUE" error.... "RagDyeR" wrote: Just add another argument to the original formula: Watch out though ... you're switching amount columns in your questions! =SUMPRODUCT((C2:C1000=DATE(2006,1,1))*(C2:C1000<= DATE(2006,3,31))*(AT2:AT10 00="Heroin")*AL2:AL1000) I would suggest that you consider using specific cells to designate your variables, so that you won't have to revise the formula itself, if and when you change time periods and drugs. A1 = start date A2 = end date A3 = drug =SUMPRODUCT((C2:C1000=A1)*(C2:C1000<=A2)*(AT2:AT1 000=A3)*AL2:AL1000) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Jeremy Ellison" wrote in message ... Very helpful, from both of you.... I think that would work. I forgot to mention that I also need to select which numbers to add within the column.... C AT AU 1 Date DRUG Amount 2 1/1/6 Heroin 2.2 Grams 3 1/2/7 Heroin 5.5 Grams I need it to add just the "heroin" -- or what ever other drug I want to add up, recovered between 1/1/6 adn 3/31/6.... teh result of this formula from above should then be 2.2 grams... Thank you again for your help...I am learning lots from reading how u put this stuff into action! "Rowan Drummond" wrote: You could try: =SUMIF($C$2:$C$21,"<=03/31/06",$AL$2:$AL$21)-SUMIF($C$2:$C$21,"<01/01/06",$A L$2:$AL$21) Format as general. Hope this helps Rowan Jeremy Ellison wrote: I want to add a column of numbers (column AL, which contains #.# Grams) if column C (dates) is between a range of dates (ie. between 1/1/06 and 3/31/06). I could use sumproduct if it was text, to count the number of occurrances of a certain text, but how to I get it to add numbers....? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers in column b if date is between certain criteria
Do you have any text in: CaseData!AU2:AU1000
Do you have any error values in any of the ranges? Jeremy Ellison wrote: =SUMPRODUCT((CaseData!C2:C1000=DATE(2005,10,1))*( CaseData!C2:C1000<=DATE(200,12,31))*(CaseData!AT2: AT1000="Heroin")*CaseData!AU2:AU1000) is what I put in there.... I got back a "VALUE" error.... "RagDyeR" wrote: Just add another argument to the original formula: Watch out though ... you're switching amount columns in your questions! =SUMPRODUCT((C2:C1000=DATE(2006,1,1))*(C2:C1000<= DATE(2006,3,31))*(AT2:AT10 00="Heroin")*AL2:AL1000) I would suggest that you consider using specific cells to designate your variables, so that you won't have to revise the formula itself, if and when you change time periods and drugs. A1 = start date A2 = end date A3 = drug =SUMPRODUCT((C2:C1000=A1)*(C2:C1000<=A2)*(AT2:AT1 000=A3)*AL2:AL1000) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Jeremy Ellison" wrote in message ... Very helpful, from both of you.... I think that would work. I forgot to mention that I also need to select which numbers to add within the column.... C AT AU 1 Date DRUG Amount 2 1/1/6 Heroin 2.2 Grams 3 1/2/7 Heroin 5.5 Grams I need it to add just the "heroin" -- or what ever other drug I want to add up, recovered between 1/1/6 adn 3/31/6.... teh result of this formula from above should then be 2.2 grams... Thank you again for your help...I am learning lots from reading how u put this stuff into action! "Rowan Drummond" wrote: You could try: =SUMIF($C$2:$C$21,"<=03/31/06",$AL$2:$AL$21)-SUMIF($C$2:$C$21,"<01/01/06",$A L$2:$AL$21) Format as general. Hope this helps Rowan Jeremy Ellison wrote: I want to add a column of numbers (column AL, which contains #.# Grams) if column C (dates) is between a range of dates (ie. between 1/1/06 and 3/31/06). I could use sumproduct if it was text, to count the number of occurrances of a certain text, but how to I get it to add numbers....? -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers in column b if date is between certain criteria
Does your Column AU actually contain the word "grams"?
I thought you were just being descriptive. If that's the case, you've got to eliminate that text. Are you importing this data, or are you keying it in? If you're entering it yourself, you can format the column to display "grams", and still retain numerical integrity. Custom format the column as: #,###.##" grams" If you're importing the data, you can try to eliminate the text en masse using TTC. Select the column and then: <Data <Text To Columns Check "delimited", then <Next. Click on "space", then look in the "preview box" and make sure all the text comes together in a single, separate column. If you don't see a separation, you might have "non-breaking" spaces. Click on "other", and then hold down <Alt, And type 0160 Using the num keypad, *not* the numbers under the function keys. If you now see separate columns, Then <Next. Click in that text column to select it, then click on "Do Not Import". The header of the text column should change to "Skip Column". Then hit <Finish. Since you're using the asterisk form of Sumproduct, it really shouldn't matter what format the remaining numbers actually are. As long as they look like numbers, they should calculate. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeremy Ellison" wrote in message ... =SUMPRODUCT((CaseData!C2:C1000=DATE(2005,10,1))*( CaseData!C2:C1000<=DATE(20 0,12,31))*(CaseData!AT2:AT1000="Heroin")*CaseData! AU2:AU1000) is what I put in there.... I got back a "VALUE" error.... "RagDyeR" wrote: Just add another argument to the original formula: Watch out though ... you're switching amount columns in your questions! =SUMPRODUCT((C2:C1000=DATE(2006,1,1))*(C2:C1000<= DATE(2006,3,31))*(AT2:AT10 00="Heroin")*AL2:AL1000) I would suggest that you consider using specific cells to designate your variables, so that you won't have to revise the formula itself, if and when you change time periods and drugs. A1 = start date A2 = end date A3 = drug =SUMPRODUCT((C2:C1000=A1)*(C2:C1000<=A2)*(AT2:AT1 000=A3)*AL2:AL1000) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Jeremy Ellison" wrote in message ... Very helpful, from both of you.... I think that would work. I forgot to mention that I also need to select which numbers to add within the column.... C AT AU 1 Date DRUG Amount 2 1/1/6 Heroin 2.2 Grams 3 1/2/7 Heroin 5.5 Grams I need it to add just the "heroin" -- or what ever other drug I want to add up, recovered between 1/1/6 adn 3/31/6.... teh result of this formula from above should then be 2.2 grams... Thank you again for your help...I am learning lots from reading how u put this stuff into action! "Rowan Drummond" wrote: You could try: =SUMIF($C$2:$C$21,"<=03/31/06",$AL$2:$AL$21)-SUMIF($C$2:$C$21,"<01/01/06",$A L$2:$AL$21) Format as general. Hope this helps Rowan Jeremy Ellison wrote: I want to add a column of numbers (column AL, which contains #.# Grams) if column C (dates) is between a range of dates (ie. between 1/1/06 and 3/31/06). I could use sumproduct if it was text, to count the number of occurrances of a certain text, but how to I get it to add numbers....? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum range of values that meet date criteria | Excel Discussion (Misc queries) | |||
dsum with date criteria | Excel Worksheet Functions | |||
need to find which numbers (3+) in a column sum to a value | Excel Discussion (Misc queries) | |||
I cannot get a column of numbers to sum, why? | Excel Worksheet Functions | |||
Formula to determine a future date based on criteria | Excel Worksheet Functions |