Excel - Sumproduct

Asked By bijan

21-Nov-09 03:19 AM

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

21-Nov-09 03:50 AM

Hi Bijan

I do not 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

21-Nov-09 05:31 AM

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

21-Nov-09 06:47 AM

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