+ Reply to Thread
Results 1 to 5 of 5

SUMIF & TRUNC question

  1. #1
    Forum Contributor
    Join Date
    02-03-2005
    Location
    Chicago, IL
    Posts
    101

    SUMIF & TRUNC question

    I am working with the formula below.

    =SUMIF('Input 505 & 506'!$C$5:$C$63,$A6,'Input 505 & 506'!$D$5:$D$63)

    It is comparing alpha characters and when it finds a match, it is pulling a number that is sitting in a third column.

    Some of the numbers may have decimals in them and I want this formula to pull the number without any decimals. I would like to "round" the number prior to it being pulled and summed. I know I can use:

    {=SUM(TRUNC('Input 505 & 506'!$D$5:$D$63))} an array
    or
    =SUMPRODUCT(TRUNC('Input 505 & 506'!$D$5:$D$63))

    Is there anyway to squeeze this into the 3rd argument of my SUMIF function? I've not had any luck so far....

  2. #2
    Bob Phillips
    Guest

    Re: SUMIF & TRUNC question

    No, you have found the alternatives.

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Celt" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I am working with the formula below.
    >
    > =SUMIF('Input 505 & 506'!$C$5:$C$63,$A6,'Input 505 & 506'!$D$5:$D$63)
    >
    > It is comparing alpha characters and when it finds a match, it is
    > pulling a number that is sitting in a third column.
    >
    > Some of the numbers may have decimals in them and I want this formula
    > to pull the number without any decimals. I would like to "round" the
    > number prior to it being pulled and summed. I know I can use:
    >
    > {=SUM(TRUNC('Input 505 & 506'!$D$5:$D$63))} an array
    > or
    > =SUMPRODUCT(TRUNC('Input 505 & 506'!$D$5:$D$63))
    >
    > Is there anyway to squeeze this into the 3rd argument of my SUMIF
    > function? I've not had any luck so far....
    >
    >
    > --
    > Celt
    > ------------------------------------------------------------------------
    > Celt's Profile:

    http://www.excelforum.com/member.php...o&userid=19413
    > View this thread: http://www.excelforum.com/showthread...hreadid=519792
    >




  3. #3
    Forum Contributor
    Join Date
    02-03-2005
    Location
    Chicago, IL
    Posts
    101

    Thumbs up

    I got it....

    instead of trying to "round" in this function, which doesn't work...or I just haven't found the right combo yet..
    =SUMIF('Input 505 & 506'!$C$5:$C$63,$A6,'Input 505 & 506'!$D$5:$D$63)

    this one works just fine.
    =SUMPRODUCT(--('Input 505 & 506'!$C$5:$C$63=$A6),TRUNC('Input 505 & 506'!$D$5:$D$63))

  4. #4
    Kevin Vaughn
    Guest

    RE: SUMIF & TRUNC question

    You could try the following array entered formula:
    =SUM(IF($B$12:$B$16="blah",INT($A$12:$A$16),0))
    or since you are familiar with sumproduct ...
    =SUMPRODUCT(--($B$12:$B$16="blah"),--INT($A$12:$A$16))
    If you want to replace int with trunc, feel free.
    --
    Kevin Vaughn


    "Celt" wrote:

    >
    > I am working with the formula below.
    >
    > =SUMIF('Input 505 & 506'!$C$5:$C$63,$A6,'Input 505 & 506'!$D$5:$D$63)
    >
    > It is comparing alpha characters and when it finds a match, it is
    > pulling a number that is sitting in a third column.
    >
    > Some of the numbers may have decimals in them and I want this formula
    > to pull the number without any decimals. I would like to "round" the
    > number prior to it being pulled and summed. I know I can use:
    >
    > {=SUM(TRUNC('Input 505 & 506'!$D$5:$D$63))} an array
    > or
    > =SUMPRODUCT(TRUNC('Input 505 & 506'!$D$5:$D$63))
    >
    > Is there anyway to squeeze this into the 3rd argument of my SUMIF
    > function? I've not had any luck so far....
    >
    >
    > --
    > Celt
    > ------------------------------------------------------------------------
    > Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
    > View this thread: http://www.excelforum.com/showthread...hreadid=519792
    >
    >


  5. #5
    Forum Contributor
    Join Date
    02-03-2005
    Location
    Chicago, IL
    Posts
    101

    Thumbs up

    Thanks for the help everyone!!!

+ 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