Sumproduct

  • Thread starter Thread starter bijan
  • Start date Start date
B

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
 
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
 
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
 
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
 
Back
Top