SUMIF & TRUNC question

[Celt]

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

 

[Bob Phillips]

No, you have found the alternatives.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Celt]

I got it....

instead of trying to "round" in this function, which doesn't work...o
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)

 

[Guest]

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.

 

[Celt]

Thanks for the help everyone!!!