Remember Me?

#1
 yak10 Posts: n/a
SUMIF function

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
#2
 Sandy Mann Posts: n/a

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" wrote in message
...

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

#3

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.

#4
 Ashish Mathur Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Shelba Excel Worksheet Functions 1 February 2nd 05 05:40 AM Frannie21 Excel Worksheet Functions 4 January 27th 05 03:28 PM Oscar Excel Worksheet Functions 2 January 11th 05 11:01 PM [email protected] Excel Worksheet Functions 3 December 17th 04 06:27 PM BMWPRO Excel Worksheet Functions 1 December 6th 04 08:26 PM

All times are GMT +1. The time now is 03:12 AM.