Extracting and calculating name of company costs in a specific range

[ambepat]

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,

 

[jtakw]

Hi,

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

Book1
	A	B	C	D	E	F
2	Chubb	$30.00	Entropy	$60.00	Ward	$220.00
3	Chubb	$40.00	Entropy	$70.00
4	Ward	$240.00	Entropy	$80.00	Chubb	$100.00
5	Chubb	$60.00			Ward	$240.00
6	Chubb	$70.00	Entropy	$100.00	Ward	$300.00
7			Entropy	$110.00
8	Entropy	$70.00	Chubb	$40.00
9	Chubb	$100.00	Entropy	$130.00	Ward	$360.00
10
11
12	Company	Total Cost
13	Chubb	$440.00
14	Entropy	$620.00
15	Ward	$1,360.00
Sheet76

Cell Formulas
Range	Formula
B13	=SUMIF(A$2:E$9,A13,B$2:F$9)

Formula copied down for other Companies.

 

[ambepat]

Hi,

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

	A	B	C	D	E	F
2	Chubb	$30.00	Entropy	$60.00	Ward	$220.00
3	Chubb	$40.00	Entropy	$70.00
4	Ward	$240.00	Entropy	$80.00	Chubb	$100.00
5	Chubb	$60.00			Ward	$240.00
6	Chubb	$70.00	Entropy	$100.00	Ward	$300.00
7			Entropy	$110.00
8	Entropy	$70.00	Chubb	$40.00
9	Chubb	$100.00	Entropy	$130.00	Ward	$360.00
10
11
12	Company	Total Cost
13	Chubb	$440.00
14	Entropy	$620.00
15	Ward	$1,360.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet76​

Worksheet Formulas Cell Formula B13 =SUMIF(A$2:E$9,A13,B$2:F$9) <thead> </thead><tbody> </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]

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:

Book1
	A	B	C	D	E	F
2	Chubb	$30.00	Entropy	$60.00	Ward	$220.00
3	Chubb	$40.00	Entropy	$70.00
4			Entropy	$80.00
5	Chubb	$60.00			Ward	$240.00
6	Chubb	$70.00	Entropy	$100.00	Ward	$300.00
7			Entropy	$110.00
8
9	Chubb	$100.00	Entropy	$130.00	Ward	$360.00
10
11
12	Company	Total Cost
13	Chubb	$300.00
14	Entropy	$550.00
15	Ward	$1,120.00
Sheet76

Cell Formulas
Range	Formula
B13	=SUMIF(A$2:E$9,A13,B$2:F$9)

 

[ambepat]

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:

	A	B	C	D	E	F
2	Chubb	$30.00	Entropy	$60.00	Ward	$220.00
3	Chubb	$40.00	Entropy	$70.00
4			Entropy	$80.00
5	Chubb	$60.00			Ward	$240.00
6	Chubb	$70.00	Entropy	$100.00	Ward	$300.00
7			Entropy	$110.00
8
9	Chubb	$100.00	Entropy	$130.00	Ward	$360.00
10
11
12	Company	Total Cost
13	Chubb	$300.00
14	Entropy	$550.00
15	Ward	$1,120.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet76​

Worksheet Formulas Cell Formula B13 =SUMIF(A$2:E$9,A13,B$2:F$9) <thead> </thead><tbody> </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]

You're Not explaining what you want, good luck.