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


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=519792

SUMIF & TRUNC question
 

No, you have found the alternatives.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Celt" wrote in message
...

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

SUMIF & TRUNC question
 

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))


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=519792

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

SUMIF & TRUNC question
 

Thanks for the help everyone!!!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=519792