+ Reply to Thread
Results 1 to 14 of 14

=SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31", Rewards!$AH$20:$BI$20)

  1. #1
    Registered User
    Join Date
    07-22-2005
    Posts
    15

    =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31", Rewards!$AH$20:$BI$20)

    =SUMIF(Rewards!$AH$3:$BI$3,Targets!O30, Rewards!$AH$20:$BI$20)
    =SUMIF(Rewards!$AH$3:$BI$3,Targets!O30, Rewards!$AH$20:$BI$20)
    In the above equation I am trying to get a total using sumif.
    The rewards cells row 3 refer to a range of automatically generated consecutive date cells.

    The target cell is a manually entered date

    The reward cells in row 20 are automatically generated numbers.

    What I am trying to do is have the calculation check for the date in Targets and then only sum the numbers that are entered on dates equal or after that entered in targets
    I also tried
    =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31", Rewards!$AH$20:$BI$20)

    in the hope of getting it to sum between two given dates.
    Any clues out there? I'm going around in circles here.

  2. #2
    Anne Troy
    Guest

    Re: =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31", Rewards!$AH$20:$BI$20)

    This one always gets me and I can never remember how to do it, so I had a
    friend write up this article, Dond:
    http://www.officearticles.com/excel/...soft_excel.htm
    ************
    Anne Troy
    www.OfficeArticles.com

    "dond" <[email protected]> wrote in message
    news:[email protected]...
    >
    > =SUMIF(Rewards!$AH$3:$BI$3,Targets!O30, Rewards!$AH$20:$BI$20)
    > =SUMIF(Rewards!$AH$3:$BI$3,Targets!O30, Rewards!$AH$20:$BI$20)
    > In the above equation I am trying to get a total using sumif.
    > The rewards cells row 3 refer to a range of automatically generated
    > consecutive date cells.
    >
    > The target cell is a manually entered date
    >
    > The reward cells in row 20 are automatically generated numbers.
    >
    > What I am trying to do is have the calculation check for the date in
    > Targets and then only sum the numbers that are entered on dates equal
    > or after that entered in targets
    > I also tried
    > =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31",
    > Rewards!$AH$20:$BI$20)
    >
    > in the hope of getting it to sum between two given dates.
    > Any clues out there? I'm going around in circles here.
    >
    >
    > --
    > dond
    > ------------------------------------------------------------------------
    > dond's Profile:
    > http://www.excelforum.com/member.php...o&userid=25477
    > View this thread: http://www.excelforum.com/showthread...hreadid=483373
    >




  3. #3
    Registered User
    Join Date
    11-09-2005
    Posts
    42
    Hi everyone,

    Could anyone help!!!

    I would like to know if I could use SUMIF function with two criteria and two ranges in it. For example, I would like to sum up a columb if any given cell in that columb fulfills two criteria. The one criteria is in one columb, the other criteria is in other columb.

    Thank you.

  4. #4
    Dave Peterson
    Guest

    Re: =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31",

    Not =sumif().

    But you can use =sumproduct()

    =sumproduct(--(a1:a10="something"),--(b1:b10="another"),c1:c10)

    =sumproduct() likes to work with numbers. The -- converts true/falses to 1/0's.

    Extend your range to what you want, but don't use the whole column.

    kras wrote:
    >
    > Hi everyone,
    >
    > Could anyone help!!!
    >
    > I would like to know if I could use SUMIF function with two criteria
    > and two ranges in it. For example, I would like to sum up a columb if
    > any given cell in that columb fulfills two criteria. The one criteria
    > is in one columb, the other criteria is in other columb.
    >
    > Thank you.
    >
    > --
    > kras
    > ------------------------------------------------------------------------
    > kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
    > View this thread: http://www.excelforum.com/showthread...hreadid=483373


    --

    Dave Peterson

  5. #5
    Registered User
    Join Date
    11-09-2005
    Posts
    42
    Hi Dave,

    Thank you for your reply.

    The problem is that one of the columbs contains letters, not numbers and sumproduct formula returns #VALUE!

    Probably I have to be more specific. I'll give you an example. So, there are three columbs in my spreadsheet. The first contains time in which a process starts /for ex. 11:24:32/. The second columb contains the duration of the process /for ex. 00:03:45/. The third columb contains the kind of process/ for example SB, SS, etc./.

    I would like to find out the average time duration of a given process /lets say SB/ after 15:30:00.

  6. #6
    Dave Peterson
    Guest

    Re: =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31",

    This formula:

    =sumproduct(--(a1:a10="something"),--(b1:b10="another"),c1:c10)

    expects to find numbers in C1:C10. If you have text/errors in that range, then
    you'll have trouble.

    (You'd have to adjust the ranges to match your data.)

    But from what you wrote in your followup, you're only looking at one criteria.

    So maybe something like:
    =SUMIF(C1:C10,"SB",B1:B10)/COUNTIF(C1:C10,"SB")

    Oopsie....

    You wanted it after 15:30:00, so back to =sumproduct()

    =sumproduct(--(a1:a10>=time(15,30,0)),--(c1:c10="sb"),b1:b10)
    / sumproduct(--(a1:a10>=time(15,30,0)),--(c1:c10="sb"))

    All one cell.

    That assumes that the times in column A are really times--they don't include
    dates.

    (Sum up all the duration times in column B that matches the criteria and divide
    it by the count that match both criteria.)



    kras wrote:
    >
    > Hi Dave,
    >
    > Thank you for your reply.
    >
    > The problem is that one of the columbs contains letters, not numbers
    > and sumproduct formula returns #VALUE!
    >
    > Probably I have to be more specific. I'll give you an example. So,
    > there are three columbs in my spreadsheet. The first contains time in
    > which a process starts /for ex. 11:24:32/. The second columb contains
    > the duration of the process /for ex. 00:03:45/. The third columb
    > contains the kind of process/ for example SB, SS, etc./.
    >
    > I would like to find out the average time duration of a given process
    > /lets say SB/ after 15:30:00.
    >
    > --
    > kras
    > ------------------------------------------------------------------------
    > kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
    > View this thread: http://www.excelforum.com/showthread...hreadid=483373


    --

    Dave Peterson

  7. #7
    Registered User
    Join Date
    07-22-2005
    Posts
    15

    Smile Many Thanks

    Hi Anne,
    Really appreciate the assistance. I would never have got that calculation and it is a very useful tip to file away for later reference. This completed the last part of a project I have been working on for months. Once again thankyou for the help.
    Dond

  8. #8
    Registered User
    Join Date
    11-09-2005
    Posts
    42
    Hi Dave,

    Thank you again for your prompt answer.

    I tried the formula you wrote down in your last post. The problem is that as soon as i write a condition such as >=time(15;30;0) or ="sb" in the formula, it returns 0. If I remove conditions from the formula it returns a result.
    Also, I looked at formula help menu. It is said there that if one of the arrays are not numeric the formula considers them as zeros. I think this is an additional problem for using this formula, because one of arrays are letter /ex. SB/.

    Do you think I could use another formula, instead of SUMPRODUCT?

    In my case, lets say, I need two levels of data filtering. The first level is to filter data by the condition "SB", and the second level is to filter the already filtered data by the condition "time". After that I have to find the average minutes a process lasts after a specific time, say 15:30:00.
    For example:

    Time In duration process
    10:23:12 2:45 SB
    10:45:05 1:12 AA
    10:56:25 0:56 SB
    15:45:14 5:23 SB

    What if I use IF functions? Can I manage this in one cell?

    Best regards,
    kras




    [QUOTE=Dave Peterson]This formula:

    =sumproduct(--(a1:a10="something"),--(b1:b10="another"),c1:c10)

    expects to find numbers in C1:C10. If you have text/errors in that range, then
    you'll have trouble.

    (You'd have to adjust the ranges to match your data.)

    But from what you wrote in your followup, you're only looking at one criteria.

    So maybe something like:
    =SUMIF(C1:C10,"SB",B1:B10)/COUNTIF(C1:C10,"SB")

    Oopsie....

    You wanted it after 15:30:00, so back to =sumproduct()

    =sumproduct(--(a1:a10>=time(15,30,0)),--(c1:c10="sb"),b1:b10)
    / sumproduct(--(a1:a10>=time(15,30,0)),--(c1:c10="sb"))

    All one cell.

    That assumes that the times in column A are really times--they don't include
    dates.

    (Sum up all the duration times in column B that matches the criteria and divide
    it by the count that match both criteria.)

  9. #9
    Dave Peterson
    Guest

    Re: =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31",

    Change the formula to look at just one row that should work ok.

    Now look at the values in the cell (leading/trailing spaces in the SB column) or
    dates/times in the time column.

    In fact, make sure that the time entered is really a time.

    Format that cell as general and what do you see. It sould be a decimal number
    between 0 and 1.

    If that doesn't work, then post the single row of data and the current formula
    you're trying.


    kras wrote:
    >
    > Hi Dave,
    >
    > Thank you again for your prompt answer.
    >
    > I tried the formula you wrote down in your last post. The problem is
    > that as soon as i write a condition such as >=time(15;30;0) or ="sb" in
    > the formula, it returns 0. If I remove conditions from the formula it
    > returns a result.
    > Also, I looked at formula help menu. It is said there that if one of
    > the arrays are not numeric the formula considers them as zeros. I think
    > this is an additional problem for using this formula, because one of
    > arrays are letter /ex. SB/.
    >
    > Do you think I could use another formula, instead of SUMPRODUCT?
    >
    > In my case, lets say, I need two levels of data filtering. The first
    > level is to filter data by the condition "SB", and the second level is
    > to filter the already filtered data by the condition "time". After that
    > I have to find the average minutes a process lasts after a specific
    > time, say 15:30:00.
    > For example:
    >
    > Time In duration process
    > 10:23:12 2:45 SB
    > 10:45:05 1:12 AA
    > 10:56:25 0:56 SB
    > 15:45:14 5:23 SB
    >
    > What if I use IF functions? Can I manage this in one cell?
    >
    > Best regards,
    > kras
    >
    > Dave Peterson Wrote:
    > > This formula:
    > >
    > > =sumproduct(--(a1:a10="something"),--(b1:b10="another"),c1:c10)
    > >
    > > expects to find numbers in C1:C10. If you have text/errors in that
    > > range, then
    > > you'll have trouble.
    > >
    > > (You'd have to adjust the ranges to match your data.)
    > >
    > > But from what you wrote in your followup, you're only looking at one
    > > criteria.
    > >
    > > So maybe something like:
    > > =SUMIF(C1:C10,"SB",B1:B10)/COUNTIF(C1:C10,"SB")
    > >
    > > Oopsie....
    > >
    > > You wanted it after 15:30:00, so back to =sumproduct()
    > >
    > > =sumproduct(--(a1:a10>=time(15,30,0)),--(c1:c10="sb"),b1:b10)
    > > / sumproduct(--(a1:a10>=time(15,30,0)),--(c1:c10="sb"))
    > >
    > > All one cell.
    > >
    > > That assumes that the times in column A are really times--they don't
    > > include
    > > dates.
    > >
    > > (Sum up all the duration times in column B that matches the criteria
    > > and divide
    > > it by the count that match both criteria.)

    >
    > --
    > kras
    > ------------------------------------------------------------------------
    > kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
    > View this thread: http://www.excelforum.com/showthread...hreadid=483373


    --

    Dave Peterson

  10. #10
    Registered User
    Join Date
    11-09-2005
    Posts
    42
    Hi Dave,

    I didn't manage to do it. I attached an excel file with the example table and the formula you provided.
    Attached Files Attached Files

  11. #11
    Dave Peterson
    Guest

    Re: =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31",

    I don't connect through excelforum--so I can't see the attachments.

    But I'm one of those people who won't open attachments.

    Maybe someone else will open it and jump in with an answer--or you can post in
    plain text.

    kras wrote:
    >
    > Hi Dave,
    >
    > I didn't manage to do it. I attached an excel file with the example
    > table and the formula you provided.
    >
    > +-------------------------------------------------------------------+
    > |Filename: Book1.zip |
    > |Download: http://www.excelforum.com/attachment.php?postid=4036 |
    > +-------------------------------------------------------------------+
    >
    > --
    > kras
    > ------------------------------------------------------------------------
    > kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
    > View this thread: http://www.excelforum.com/showthread...hreadid=483373


    --

    Dave Peterson

  12. #12
    Registered User
    Join Date
    11-09-2005
    Posts
    42
    Sure Dave,

    I'm posting a plain text below:

    ColumbA/ColumbB/ColumbC/ColumbD
    Time In Time out Duration Process
    10:43:36 10:45:49 02:13 SB
    10:50:34 10:50:52 00:18 BB
    15:40:44 15:43:08 02:24 SB
    15:48:25 15:49:00 00:35 SB
    11:27:00 11:28:14 01:14 BB

    Single row formula: =SUMPRODUCT((A4>=TIME(15;30;0));(D4="SB");C4). I didn't divided it to SUMPRODUCT((A4>=TIME(15;30;0));(D4="SB")) purposely and saw that it returns #VALUE!

    Single row formula: =SUMPRODUCT((A4>=TIME(15;30;0));(D4="SB");C4)/SUMPRODUCT((A4>=TIME(15;30;0));(D4="SB"))
    It returns #VALUE!

    The Time In and Time Out cells are set to: hh:mm:ss.
    The Duration cells are set to: mm:ss
    The Process cell is set to: general
    Formula cell is set to: general

  13. #13
    Dave Peterson
    Guest

    Re: =SUMIF(Rewards!$AH$3:$BI$3, ">Targets!O30:<O31",

    You seem to have dropped some double negative signs.

    This worked with my USA settings (comma vs. semicolon)

    =SUMPRODUCT(--(A4:A99>=TIME(15,30,0)),--(D4:D99="SB"),C4:C99)
    /SUMPRODUCT(--(A4:A99>=TIME(15,30,0)),--(D4:D99="SB"))

    Watchout for typos:

    =SUMPRODUCT(--(A4:A99>=TIME(15;30;0));--(D4:D99="SB");C4:C99)
    /SUMPRODUCT(--(A4:A99>=TIME(15;30;0));--(D4:D99="SB"))


    =sumproduct() likes to work with numbers. The -- stuff converts true and false
    to 1 and 0.


    kras wrote:
    >
    > Sure Dave,
    >
    > I'm posting a plain text below:
    >
    > ColumbA/ColumbB/ColumbC/ColumbD
    > Time In Time out Duration Process
    > 10:43:36 10:45:49 02:13 SB
    > 10:50:34 10:50:52 00:18 BB
    > 15:40:44 15:43:08 02:24 SB
    > 15:48:25 15:49:00 00:35 SB
    > 11:27:00 11:28:14 01:14 BB
    >
    > Single row formula: =SUMPRODUCT((A4>=TIME(15;30;0));(D4="SB");C4). I
    > didn't divided it to SUMPRODUCT((A4>=TIME(15;30;0));(D4="SB"))
    > purposely and saw that it returns #VALUE!
    >
    > Single row formula:
    > =SUMPRODUCT((A4>=TIME(15;30;0));(D4="SB");C4)/SUMPRODUCT((A4>=TIME(15;30;0));(D4="SB"))
    > It returns #VALUE!
    >
    > The Time In and Time Out cells are set to: hh:mm:ss.
    > The Duration cells are set to: mm:ss
    > The Process cell is set to: general
    > Formula cell is set to: general
    >
    > --
    > kras
    > ------------------------------------------------------------------------
    > kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
    > View this thread: http://www.excelforum.com/showthread...hreadid=483373


    --

    Dave Peterson

  14. #14
    Registered User
    Join Date
    11-09-2005
    Posts
    42
    Hi Dave,

    Wow!!! It works now. Thank you very much for your precious help. I really appreciate it.

    After using this formula it saved me a lot of redundant columbs.


    Best regards,
    kras

+ 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