SUMPRODUCT
(1)
BijanI
(1)
Hopes
(1)
Dosent
(1)
Comme
(1)
Yyyy
(1)

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

Hi BijanI do not see anyting wrong with your formula.

Per Jessen replied to 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

Hi Jessen,It dosen't work again,it seems I could not use sumproduct function

bijan replied to Per Jessen
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

Bijan,Using two or more conditions in one column is no problem, so I guess

Per Jessen replied to 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
Post Question To EggHeadCafe