Does anyone know how to do a SUMIF which will allow you to enter two ranges
which apply to two sets of criteria? For example, I have the following data,
and I would like to write a formula that will sum for me the red Ds only.
(answer = 3)

colour letter amount
red F 3
orange D 5
yellow B 4
purple E 2
red D 3
orange B 5
yellow E 4
purple F 2

I normally write this in two steps, but I wonder if it is possible to do it
all in one formula?

=SUMPRODUCT((A2:A9="red")*(B2:B9="D")*(C2:C9))

Regards

Trevor


"Plum" wrote in message
...
Does anyone know how to do a SUMIF which will allow you to enter two
ranges
which apply to two sets of criteria? For example, I have the following
data,
and I would like to write a formula that will sum for me the red Ds only.
(answer = 3)

colour letter amount
red F 3
orange D 5
yellow B 4
purple E 2
red D 3
orange B 5
yellow E 4
purple F 2

I normally write this in two steps, but I wonder if it is possible to do
it
all in one formula?

Use SUMPRODUCT:

=SUMPRODUCT(--(A1:A8="red"),--(B1:B8="D")*(C1:C8))

you can use it for many criteria
--
Gary''s Student - gsnu200717


"Plum" wrote:

Does anyone know how to do a SUMIF which will allow you to enter two ranges
which apply to two sets of criteria? For example, I have the following data,
and I would like to write a formula that will sum for me the red Ds only.
(answer = 3)

colour letter amount
red F 3
orange D 5
yellow B 4
purple E 2
red D 3
orange B 5
yellow E 4
purple F 2

I normally write this in two steps, but I wonder if it is possible to do it
all in one formula?

perfect - thanks so much.

"Gary''s Student" wrote:

Use SUMPRODUCT:

=SUMPRODUCT(--(A1:A8="red"),--(B1:B8="D")*(C1:C8))

you can use it for many criteria
--
Gary''s Student - gsnu200717


"Plum" wrote:

Does anyone know how to do a SUMIF which will allow you to enter two ranges
which apply to two sets of criteria? For example, I have the following data,
and I would like to write a formula that will sum for me the red Ds only.
(answer = 3)

colour letter amount
red F 3
orange D 5
yellow B 4
purple E 2
red D 3
orange B 5
yellow E 4
purple F 2

I normally write this in two steps, but I wonder if it is possible to do it
all in one formula?

Does anyone know how to do a SUMIF...
which apply to two sets of criteria?

Hi. If using range names in Excel 2007...

=SUMIFS(Number,Color,"Red",Letter,"D")

--
HTH :)
Dana DeLouis
Windows XP & Office 2007


"Plum" wrote in message
...
Does anyone know how to do a SUMIF which will allow you to enter two
ranges
which apply to two sets of criteria? For example, I have the following
data,
and I would like to write a formula that will sum for me the red Ds only.
(answer = 3)

colour letter amount
red F 3
orange D 5
yellow B 4
purple E 2
red D 3
orange B 5
yellow E 4
purple F 2

I normally write this in two steps, but I wonder if it is possible to do
it
all in one formula?