Extracting and calculating name of company costs in a specific range

ambepat

Board Regular
Joined
May 4, 2014
Messages
127
Hi guys,

Really need your help with below if possible.

Below is a table of company name and costs

Name of CompanyCost To MaintainName of CompanyCost To MaintainName of CompanyCost To Maintain
Chubb£30.00Entropy£60.00Ward£220.00
Chubb£40.00Entropy£70.00
Entropy£80.00
Chubb£60.00Ward£240.00
Chubb£70.00Entropy£100.00Ward£300.00
Entropy£110.00
Chubb£100.00Entropy£130.00Ward£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,
 

Some videos you may like

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
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Chubb</td><td style="text-align: right;;">$30.00</td><td style=";">Entropy</td><td style="text-align: right;;">$60.00</td><td style=";">Ward</td><td style="text-align: right;;">$220.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Chubb</td><td style="text-align: right;;">$40.00</td><td style=";">Entropy</td><td style="text-align: right;;">$70.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Ward</td><td style="text-align: right;;">$240.00</td><td style=";">Entropy</td><td style="text-align: right;;">$80.00</td><td style=";">Chubb</td><td style="text-align: right;;">$100.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Chubb</td><td style="text-align: right;;">$60.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Ward</td><td style="text-align: right;;">$240.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Chubb</td><td style="text-align: right;;">$70.00</td><td style=";">Entropy</td><td style="text-align: right;;">$100.00</td><td style=";">Ward</td><td style="text-align: right;;">$300.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Entropy</td><td style="text-align: right;;">$110.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Entropy</td><td style="text-align: right;;">$70.00</td><td style=";">Chubb</td><td style="text-align: right;;">$40.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Chubb</td><td style="text-align: right;;">$100.00</td><td style=";">Entropy</td><td style="text-align: right;;">$130.00</td><td style=";">Ward</td><td style="text-align: right;;">$360.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Company</td><td style=";">Total Cost</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Chubb</td><td style="text-align: right;;">$440.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Entropy</td><td style="text-align: right;;">$620.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Ward</td><td style="text-align: right;;">$1,360.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet76</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B13</th><td style="text-align:left">=SUMIF(<font color="Blue">A$2:E$9,A13,B$2:F$9</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down for other Companies.
 

ambepat

Board Regular
Joined
May 4, 2014
Messages
127
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

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

Worksheet Formulas
CellFormula
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Chubb</td><td style="text-align: right;;">$30.00</td><td style=";">Entropy</td><td style="text-align: right;;">$60.00</td><td style=";">Ward</td><td style="text-align: right;;">$220.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Chubb</td><td style="text-align: right;;">$40.00</td><td style=";">Entropy</td><td style="text-align: right;;">$70.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Entropy</td><td style="text-align: right;;">$80.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Chubb</td><td style="text-align: right;;">$60.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Ward</td><td style="text-align: right;;">$240.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Chubb</td><td style="text-align: right;;">$70.00</td><td style=";">Entropy</td><td style="text-align: right;;">$100.00</td><td style=";">Ward</td><td style="text-align: right;;">$300.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Entropy</td><td style="text-align: right;;">$110.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Chubb</td><td style="text-align: right;;">$100.00</td><td style=";">Entropy</td><td style="text-align: right;;">$130.00</td><td style=";">Ward</td><td style="text-align: right;;">$360.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Company</td><td style=";">Total Cost</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Chubb</td><td style="text-align: right;;">$300.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Entropy</td><td style="text-align: right;;">$550.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Ward</td><td style="text-align: right;;">$1,120.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet76</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B13</th><td style="text-align:left">=SUMIF(<font color="Blue">A$2:E$9,A13,B$2:F$9</font>)</td></tr></tbody></table></td></tr></table><br />
 

ambepat

Board Regular
Joined
May 4, 2014
Messages
127
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

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

Worksheet Formulas
CellFormula
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
You're Not explaining what you want, good luck.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,293
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top