You "formula" people give me a headache! <vbg



"T. Valko" wrote:

Ah, what perfect timing!

Here's an example of where this behavior comes into play:

http://tinyurl.com/ypb5km

You'll notice in my formula I use OFFSET. This is so that the mentioned
behavior in SUMIF doesn't "accidentally" include any cells below A1. Using
OFFSET limits the SUMIF(arg1,....arg3) to be the same size.

Biff

"Dave Peterson" wrote in message
...
I would never even thought to have used this syntax for the 3rd argument
(or
even 2nd <bg).

Ron Coderre wrote:

Um....all references to the "2nd" argument should be to the "3rd"
argument
(more coffee anyone?).

=SUMIF(1st_arg, 2nd_arg, 3rd_arg)

Ron

"Ron Coderre" wrote in message
...
Hi, Dave

I believe the SUMIF function ignores the size of the 2nd argument's
range.
Instead, it uses a range that is the same size as the 1st argument's
range...but, begins in the first cell refenced in the 2nd argument.
That's why SUMIF works if you only use a 1-cell reference in the 2nd
argument.

Example:
These variations all return the same value
=SUMIF(A1:A10,"DAVE",B1:B10)
=SUMIF(A1:A10,"DAVE",B1:B3)
=SUMIF(A1:A10,"DAVE",B1)

Best Regards,

Ron

"Dave Peterson" wrote in message
...
Could it be that the ranges don't match: 1:30 in column A vs 1:75 in
column C?



BAC wrote:

XP Pro/Office 2003 PRO

Is there a limit on the number of characters that can be used in the
criteria section of a SUMIF()/COUNTIF() function?

Columns A & B consists of strings with a length of 30 to 50
characters.

I get incorrect results when I try to SUMIF($A$1:$A$30, "=" & B2,
$C$1:$C$75) (This formula is copied down so B2 becomes B3;B4;B5...).
Is
this
a SUMIF limit problem or do I need to look elsewhere for the weird
results
I'm getting?

TIA
BAC

--

Dave Peterson



--

Dave Peterson

--

Dave Peterson