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

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

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

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