Multiple Condition Sumif Formula

[momtoaj]

I'm trying to create a formula for the following
conditions:

column J Column I Column E
Month: Billed: Revenue:
12/1/2004 x 5,000
11/1/2004 2,500
10/1/2004 x 1,000
10/1/2004 2,000
1/1/2005 3,000
1/1/2005 x 4,000

So I want the formula to look at if the month is October
(or 10/1/2004) & if it was billed (x), then sum the
revenue column.

This is the formula that I tried, but it is still coming
back with a $0 answer:

=SUMPRODUCT((J5:J44="10/1/2004")*(I5:I44="x")*(E5:E44))
The result should be $1,000

I've tried to enter the formula exactly as it is written
& I've tried to enter the formula as an array by doing
CTRL+SHIFT+ENTER, but I still get $0

What am I doing wrong?, or if there is a better formula
to use...

Thanks!

 

[Guest]

Hi

I think your problem might be that your 'date' (in the Month column) is a
date but your 'date' in your formula is classed as text. Try typing
10/1/2004 (as a date) in a cell on its own (say X1), and use:
=SUMPRODUCT((J5:J44=X1)*(I5:I44="x")*(E5:E44))
At least that will tell you if we're on the right track!

 

[momtoaj]

You're right, that fixed it!

Thanks a bunch!

-----Original Message-----
Hi

I think your problem might be that your 'date' (in the Month column) is a
date but your 'date' in your formula is classed as text. Try typing
10/1/2004 (as a date) in a cell on its own (say X1), and use:
=SUMPRODUCT((J5:J44=X1)*(I5:I44="x")*(E5:E44))
At least that will tell you if we're on the right track!

--
Andy.





.

 

[Guest]

Great! If you want to keep your original formula, then, you'll need to
change it to:
=SUMPRODUCT((J5:J44=DATEVALUE("10/1/2004"))*(I5:I44="x")*(E5:E44))

Thanks for the feedback!