Sumproduct

[bijan]

Hi all,
I should make a condition(AND) in my sumproduct formula with date
format(yyyy/mm/dd), why it dosent accept my condition in one column, like
this
=SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000))

even I dublicate date column(G) and change the formula to
=SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000))
but it dosen't work again, any idea to solve this problem would be
appreciated.
Bijan

 

[Per Jessen]

Hi Bijan

I don't see anyting wrong with your formula.

Instead of multiplying each statement, seperate each statement by a comme
and use double unary to convert true/false to 1/0. Now you can evaluate your
formula. When the formula cell is selected click the equal sign next to the
formula line. Maybe you can see why it does not work now.

=SUMPRODUCT(--($B$30:$B$3000=$A$1),--($G$30:$G$3000>=$B$1),--($G$30:$G$3000<$B$2),$J$30:$J$3000)

Hopes this helps.
....
Per

 

[bijan]

Hi Jessen,
It dosen't work again,it seems I couldn't use sumproduct function with two
condition in a column at same time or formula/my data have a problem that I
can't see it?
Bijan

 

[Per Jessen]

Bijan,

Using two or more conditions in one column is no problem, so I guess the
problem is related to you data.

Is the dates in column G and in B1:B2 entered as true dates?

Best regards,
Per