Prev: simultaneous v&h lookup - approaches...
Next: SUMIF
From: yak10 on 11 Feb 2005 15:51

Hi everyone, first post for me,

I have been using the sumif function the last couple days and I've was
wondering if there is anyway to have more than 1 range and criteria for
this function or is there another function where I can do this? Below
might better illustrate what I'm trying to do:

Column 1 Column 2 Column 3
Blue One 100
Blue One 100
Red One 100
Red Two 200

I want the sum of column 3, if column 1 equals blue AND column 2 equals
One. So my answer in this case is 200.


--
yak10
From: Sandy Mann on 11 Feb 2005 17:44
Try:

Sumproduct((Column1 Range="Blue")*(Column2 Range="One")*(Column3 Range))

All three ranges must be the same length

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"yak10" <yak10.1kb65m(a)news.officefrustration.com> wrote in message
news:yak10.1kb65m(a)news.officefrustration.com...
>
> Hi everyone, first post for me,
>
> I have been using the sumif function the last couple days and I've was
> wondering if there is anyway to have more than 1 range and criteria for
> this function or is there another function where I can do this? Below
> might better illustrate what I'm trying to do:
>
> Column 1 Column 2 Column 3
> Blue One 100
> Blue One 100
> Red One 100
> Red Two 200
>
> I want the sum of column 3, if column 1 equals blue AND column 2 equals
> One. So my answer in this case is 200.
>
>
> --
> yak10


From: Aladin Akyurek on 11 Feb 2005 17:52
You need to invoke a differen type of formula for SumIf does not admit
more than one condition...

=SUMPRODUCT(--(ColorRange=Color),--(TextNumRange=TextNum),SumRange)

yak10 wrote:
> Hi everyone, first post for me,
>
> I have been using the sumif function the last couple days and I've was
> wondering if there is anyway to have more than 1 range and criteria for
> this function or is there another function where I can do this? Below
> might better illustrate what I'm trying to do:
>
> Column 1 Column 2 Column 3
> Blue One 100
> Blue One 100
> Red One 100
> Red Two 200
>
> I want the sum of column 3, if column 1 equals blue AND column 2 equals
> One. So my answer in this case is 200.
>
>
From: Ashish Mathur on 11 Feb 2005 20:15
Hi,

Array entet the following formula (Ctrl+Shift+Enter)

=SUM(IF((A2:A6=A8)*(B2:B6=1),C2:C6))

You can also use the DSUM function.

Regards,

"yak10" wrote:

>
> Hi everyone, first post for me,
>
> I have been using the sumif function the last couple days and I've was
> wondering if there is anyway to have more than 1 range and criteria for
> this function or is there another function where I can do this? Below
> might better illustrate what I'm trying to do:
>
> Column 1 Column 2 Column 3
> Blue One 100
> Blue One 100
> Red One 100
> Red Two 200
>
> I want the sum of column 3, if column 1 equals blue AND column 2 equals
> One. So my answer in this case is 200.
>
>
> --
> yak10
>
 | 
Pages: 1
Prev: simultaneous v&h lookup - approaches...
Next: SUMIF