sumproduct by date
 

Here is the formula I tried.
=SUMPRODUCT(--(E3:E27=TODAY()),--(E3:E27=TODAY()-1),(H3:H27))
Column E has dates. Column H has the numbers to total. I need the total of
the numbers in Column H if the date is either today or yesterday. This
formula always returns a 0.
--
maryj

Try:

=SUMPRODUCT(--(E3:E27=(TODAY()-1)),(H3:H27))



"maryj" wrote:

Here is the formula I tried.
=SUMPRODUCT(--(E3:E27=TODAY()),--(E3:E27=TODAY()-1),(H3:H27))
Column E has dates. Column H has the numbers to total. I need the total of
the numbers in Column H if the date is either today or yesterday. This
formula always returns a 0.
--
maryj

Your formula returns 0 because you are specifying that the date in col. E
equal today *and* yesterday. Try this instead:

=SUMPRODUCT(((E3:E27=TODAY())+(E3:E27=TODAY()-1))*(H3:H27))

or

=SUMPRODUCT(((E3:E27=TODAY())+(E3:E27=TODAY()-1)),H3:H27)

The "+" operator acts like OR.

HTH
Jason
Atlanta, GA


"maryj" wrote:

Here is the formula I tried.
=SUMPRODUCT(--(E3:E27=TODAY()),--(E3:E27=TODAY()-1),(H3:H27))
Column E has dates. Column H has the numbers to total. I need the total of
the numbers in Column H if the date is either today or yesterday. This
formula always returns a 0.
--
maryj

Thank you! That did it.
--
maryj


"Jason Morin" wrote:

Your formula returns 0 because you are specifying that the date in col. E
equal today *and* yesterday. Try this instead:

=SUMPRODUCT(((E3:E27=TODAY())+(E3:E27=TODAY()-1))*(H3:H27))

or

=SUMPRODUCT(((E3:E27=TODAY())+(E3:E27=TODAY()-1)),H3:H27)

The "+" operator acts like OR.

HTH
Jason
Atlanta, GA


"maryj" wrote:

Here is the formula I tried.
=SUMPRODUCT(--(E3:E27=TODAY()),--(E3:E27=TODAY()-1),(H3:H27))
Column E has dates. Column H has the numbers to total. I need the total of
the numbers in Column H if the date is either today or yesterday. This
formula always returns a 0.
--
maryj