Summing based on 2 criteria

I am trying to get a cell to sum based on 2 different ranges. Example, I have one column called status (which has a range of 0-5) and one called Type (which is based on SF, Acres, and Units). I've also got a 3rd column that is called size and it contains only numeric values that need to be summed up.

I was trying to figure out how I could create a formula that is able to search all of the 1s in the first column and match it up with all of the "Acres" in the other column and give me a total summed up value based on a third numeric column.

There is a very powerful tool called SUMPRODUCT() that can help you. There
is also a really good explanation at:

http://www.contextures.com/xlFunctio...tml#SumProduct
--
Gary's Student


"cubsfan" wrote:

>
> I am trying to get a cell to sum based on 2 different ranges. Example, I
> have one column called status (which has a range of 0-5) and one called
> Type (which is based on SF, Acres, and Units). I've also got a 3rd
> column that is called size and it contains only numeric values that
> need to be summed up.
>
> I was trying to figure out how I could create a formula that is able to
> search all of the 1s in the first column and match it up with all of the
> "Acres" in the other column and give me a total summed up value based on
> a third numeric column.
>
>
> --
> cubsfan
> ------------------------------------------------------------------------
> cubsfan's Profile: http://www.excelforum.com/member.php...o&userid=33272
> View this thread: http://www.excelforum.com/showthread...hreadid=534598
>
>

=SUMPRODUCT(--(A2:A200=1),--(B2:B200="Acres"),C2:C200)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cubsfan" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to get a cell to sum based on 2 different ranges. Example, I
> have one column called status (which has a range of 0-5) and one called
> Type (which is based on SF, Acres, and Units). I've also got a 3rd
> column that is called size and it contains only numeric values that
> need to be summed up.
>
> I was trying to figure out how I could create a formula that is able to
> search all of the 1s in the first column and match it up with all of the
> "Acres" in the other column and give me a total summed up value based on
> a third numeric column.
>
>
> --
> cubsfan
> ------------------------------------------------------------------------
> cubsfan's Profile:
http://www.excelforum.com/member.php...o&userid=33272
> View this thread: http://www.excelforum.com/showthread...hreadid=534598
>