# Extracting and calculating name of company costs in a specific range

#### ambepat

##### Board Regular
Hi guys,

Really need your help with below if possible.

Below is a table of company name and costs

 Name of Company Cost To Maintain Name of Company Cost To Maintain Name of Company Cost To Maintain Chubb £30.00 Entropy £60.00 Ward £220.00 Chubb £40.00 Entropy £70.00 Entropy £80.00 Chubb £60.00 Ward £240.00 Chubb £70.00 Entropy £100.00 Ward £300.00 Entropy £110.00 Chubb £100.00 Entropy £130.00 Ward £360.00

<tbody>
</tbody>

It ranges from A1 to F9.

Basically I am looking for a way to highlight all of the range A1 to F9 and extract a specific company range for example chubb and adds its total costs.

I don't want to do it by highlighting a single range of columns. I want to do it by highlighting the whole range from A1 to F9.

Is there a way of doing this or am I streching it?

Thanks,

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### jtakw

##### Well-known Member
Hi,

Not 100% sure what you're asking, is this what you mean?

Formula copied down for other Companies.

#### ambepat

##### Board Regular
Hi,

Not 100% sure what you're asking, is this what you mean?

ABCDEF
2Chubb\$30.00Entropy\$60.00Ward\$220.00
3Chubb\$40.00Entropy\$70.00
4Ward\$240.00Entropy\$80.00Chubb\$100.00
5Chubb\$60.00Ward\$240.00
6Chubb\$70.00Entropy\$100.00Ward\$300.00
7Entropy\$110.00
8Entropy\$70.00Chubb\$40.00
9Chubb\$100.00Entropy\$130.00Ward\$360.00
10
11
12CompanyTotal Cost
13Chubb\$440.00
14Entropy\$620.00
15Ward\$1,360.00

</tbody>
Sheet76

Worksheet Formulas
CellFormula
B13=SUMIF(A\$2:E\$9,A13,B\$2:F\$9)

</tbody>

<tbody>
</tbody>

Formula copied down for other Companies.

You get the idea but with your sumif you did not include column F and in the sum range you did not include column A. You also reorganise my data. Not sure why you did that.

The data does not change. It is as the way I set it originally. I just need a way for the formula to highlight all rows and columns from A to F where the data is and somehow extract the criteria a specific company e.g Chubb and add all of its costs. The formula must highlight all the hold data and somehow extract the array that matches the criteria for a specific company and add the costs. Not sure if it is possible to do. Thanks.

#### jtakw

##### Well-known Member
What do you mean by "highlight", are you talking about Conditional Formatting??

I added more data to your table because I "thought" that's what you meant by "SUM" the entire range, and Not including the last column in the Criteria Range, but Including the last column in the Sum Range is how my formula works:

#### ambepat

##### Board Regular
What do you mean by "highlight", are you talking about Conditional Formatting??

I added more data to your table because I "thought" that's what you meant by "SUM" the entire range, and Not including the last column in the Criteria Range, but Including the last column in the Sum Range is how my formula works:

ABCDEF
2Chubb\$30.00Entropy\$60.00Ward\$220.00
3Chubb\$40.00Entropy\$70.00
4Entropy\$80.00
5Chubb\$60.00Ward\$240.00
6Chubb\$70.00Entropy\$100.00Ward\$300.00
7Entropy\$110.00
8
9Chubb\$100.00Entropy\$130.00Ward\$360.00
10
11
12CompanyTotal Cost
13Chubb\$300.00
14Entropy\$550.00
15Ward\$1,120.00

</tbody>
Sheet76

Worksheet Formulas
CellFormula
B13=SUMIF(A\$2:E\$9,A13,B\$2:F\$9)

</tbody>

<tbody>
</tbody>

Your solution is not exactly what I am looking for. I am looking more for an array formula. Thanks for trying.

#### jtakw

##### Well-known Member
You're Not explaining what you want, good luck.

Replies
1
Views
348
Replies
4
Views
940
Replies
14
Views
5K
Replies
7
Views
744
Replies
9
Views
214