Match / Sum Values

R

RayG

In column B i have a list of area names ( cp, ss, dr, sp ) and in column J
and L have values in the row for these areas. I want to have a formula to say
( count all the cp, ss, dr, and sp) and sum the values in column J to match
each area and sum values in column L to match each area.
I though of doing vlookup for each area then suming the vlookup. Is there
and better way? Having a formula for each area would be fine because i have
to show the totals for each area anyway.
 
A

Arvi Laanemets

Hi

=SUMPRODUCT(--($B$2:$B$100="cp"),$J$2:$J$100)+SUMPRODUCT(--($B$2:$B$100="cp"
),$L$2:$L$100)
or
=SUMIF($B$2:$B$100,"cp",$J$2:$J$100)+SUMIF($B$2:$B$100,"cp",$L$2:$L$100)
 
Top