Multiple Condition Sumif Formula

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!

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.


"momtoaj" <[email protected]> wrote in message
news:[email protected]...
> 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!

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.
>
>
>"momtoaj" <[email protected]> wrote in
message
>news:[email protected]...
>> 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!
>
>
>.
>

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!
--
Andy.


"momtoaj" <[email protected]> wrote in message
news:[email protected]...
> 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.
>>
>>
>>"momtoaj" <[email protected]> wrote in
> message
>>news:[email protected]...
>>> 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!
>>
>>
>>.
>>
>