+ Reply to Thread
Results 1 to 4 of 4

Multiple Condition Sumif Formula

  1. #1
    momtoaj
    Guest

    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!

  2. #2
    Guest

    Re: Multiple Condition Sumif Formula

    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" <anonymous@discussions.microsoft.com> wrote in message
    news:0f9701c53ab4$3fee8e10$a501280a@phx.gbl...
    > 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!




  3. #3
    momtoaj
    Guest

    Re: Multiple Condition Sumif Formula

    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" <anonymous@discussions.microsoft.com> wrote in

    message
    >news:0f9701c53ab4$3fee8e10$a501280a@phx.gbl...
    >> 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!

    >
    >
    >.
    >



  4. #4
    Guest

    Re: Multiple Condition Sumif Formula

    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" <anonymous@discussions.microsoft.com> wrote in message
    news:05ea01c53ab9$4dfeeb80$a401280a@phx.gbl...
    > 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" <anonymous@discussions.microsoft.com> wrote in

    > message
    >>news:0f9701c53ab4$3fee8e10$a501280a@phx.gbl...
    >>> 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!

    >>
    >>
    >>.
    >>

    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1