Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif, SumProduct, CountIf Formula Help
I need help with two formulas. The first is I want a formula that would take
column A below and add the totals that are in column B that are between 3/1/08 and 3/24/08 in column A. The total I would like in C1. Also is there a formula that can be written to take the date on the computer back 10 days and use column A to give a total in B that is in this date range? Thank you for your help. A B C 1 3/23/08 26.48 2 4/20/08 0.00 3 4/16/08 2.48 4 3/18/08 46.81 5 3/10/08 0.00 6 4/12/08 42.00 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif, SumProduct, CountIf Formula Help
=SUMPRODUCT(IF(AND(A1:A6=DATEVALUE("3/1/2008"),A1:A6<=DATEVALUE("3/24/2008")),1,0)*B1:B6)
To get 10 days earlier subtract 10 from the dates =SUMPRODUCT(IF(AND(A1:A6=(DATEVALUE("3/1/2008")-10),A1:A6<=(DATEVALUE("3/24/2008")-10),1,0)*B1:B6) "Jeremy" wrote: I need help with two formulas. The first is I want a formula that would take column A below and add the totals that are in column B that are between 3/1/08 and 3/24/08 in column A. The total I would like in C1. Also is there a formula that can be written to take the date on the computer back 10 days and use column A to give a total in B that is in this date range? Thank you for your help. A B C 1 3/23/08 26.48 2 4/20/08 0.00 3 4/16/08 2.48 4 3/18/08 46.81 5 3/10/08 0.00 6 4/12/08 42.00 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif, SumProduct, CountIf Formula Help
First formula - put this in C1:
=SUMPRODUCT((A1:A6DATE(2008,3,1))*(A1:A6<DATE(200 8,3,24))*(B1:B6)) Second formula: =SUMPRODUCT((A1:A6TODAY()-10)*(A1:A6<TODAY())*(B1:B6)) If you want to include the start and end dates then change and < to = and <=. Change the ranges to suit your actual data. Hope this helps. Pete On Apr 23, 4:06*pm, Jeremy wrote: I need help with two formulas. *The first is I want a formula that would take column A below and add the totals that are in column B that are between 3/1/08 and 3/24/08 in column A. *The total I would like in C1. * Also is there a formula that can be written to take the date on the computer back 10 days and use column A to give a total in B that is in this date range? Thank you for your help. * * * * A * * * * * * * * B * * * * * * C * * * * * * * 1 * * * 3/23/08 * * * * 26.48 2 * * * 4/20/08 * * * * 0.00 3 * * * 4/16/08 * * * * 2.48 4 * * * 3/18/08 * * * * 46.81 5 * * * 3/10/08 * * * * 0.00 6 * * * 4/12/08 * * * * 42.00 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif, SumProduct, CountIf Formula Help
Did you actually try those?
First one =SUMPRODUCT(--($A$2:$A$200=--"2008-03-01"),--($A$2:$A$200<=--"2008-03-24"),$B$2:$B$200) Jeremy, you state 10 days ago as one o=end of the range, what is the other. I assumed today for this formula =SUMPRODUCT(--($A$2:$A$200=TODAY()-10),--($A$2:$A$200<=TODAY()),$B$2:$B$200) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joel" wrote in message ... =SUMPRODUCT(IF(AND(A1:A6=DATEVALUE("3/1/2008"),A1:A6<=DATEVALUE("3/24/2008")),1,0)*B1:B6) To get 10 days earlier subtract 10 from the dates =SUMPRODUCT(IF(AND(A1:A6=(DATEVALUE("3/1/2008")-10),A1:A6<=(DATEVALUE("3/24/2008")-10),1,0)*B1:B6) "Jeremy" wrote: I need help with two formulas. The first is I want a formula that would take column A below and add the totals that are in column B that are between 3/1/08 and 3/24/08 in column A. The total I would like in C1. Also is there a formula that can be written to take the date on the computer back 10 days and use column A to give a total in B that is in this date range? Thank you for your help. A B C 1 3/23/08 26.48 2 4/20/08 0.00 3 4/16/08 2.48 4 3/18/08 46.81 5 3/10/08 0.00 6 4/12/08 42.00 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF? SUMIF? SUMPRODUCT? IF? | Excel Worksheet Functions | |||
sumif, countif, sumproduct????? | Excel Worksheet Functions | |||
Sumproduct, If, Sumif, Countif, Match?? | Excel Discussion (Misc queries) | |||
countif, sumif, sumproduct - I dont know which to use | Excel Worksheet Functions | |||
Which formula to use? countif, sumif, sumproduct | Excel Discussion (Misc queries) |