Extracting and calculating name of company costs in a specific range

A

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

[TABLE="class: cms_table, width: 720"]
<tbody>[TR]
[TD]Name of Company[/TD]
[TD]Cost To Maintain[/TD]
[TD]Name of Company[/TD]
[TD]Cost To Maintain[/TD]
[TD]Name of Company[/TD]
[TD]Cost To Maintain[/TD]
[/TR]
[TR]
[TD]Chubb[/TD]
[TD]£30.00[/TD]
[TD]Entropy[/TD]
[TD]£60.00[/TD]
[TD]Ward[/TD]
[TD]£220.00[/TD]
[/TR]
[TR]
[TD]Chubb[/TD]
[TD]£40.00[/TD]
[TD]Entropy[/TD]
[TD]£70.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Entropy[/TD]
[TD]£80.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chubb[/TD]
[TD]£60.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Ward[/TD]
[TD]£240.00[/TD]
[/TR]
[TR]
[TD]Chubb[/TD]
[TD]£70.00[/TD]
[TD]Entropy[/TD]
[TD]£100.00[/TD]
[TD]Ward[/TD]
[TD]£300.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Entropy[/TD]
[TD]£110.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chubb[/TD]
[TD]£100.00[/TD]
[TD]Entropy[/TD]
[TD]£130.00[/TD]
[TD]Ward[/TD]
[TD]£360.00[/TD]
[/TR]
</tbody>[/TABLE]


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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Hi,

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


Book1
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
Sheet76
Cell Formulas
RangeFormula
B13=SUMIF(A$2:E$9,A13,B$2:F$9)


Formula copied down for other Companies.
 
Upvote 0
jtakw said:
Hi,

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

ABCDEF
ChubbEntropyWard
ChubbEntropy
WardEntropyChubb
ChubbWard
ChubbEntropyWard
Entropy
EntropyChubb
ChubbEntropyWard
CompanyTotal Cost
Chubb
Entropy
Ward

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]$30.00[/TD]

[TD="align: right"]$60.00[/TD]

[TD="align: right"]$220.00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$40.00[/TD]

[TD="align: right"]$70.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$240.00[/TD]

[TD="align: right"]$80.00[/TD]

[TD="align: right"]$100.00[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$60.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$240.00[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$70.00[/TD]

[TD="align: right"]$100.00[/TD]

[TD="align: right"]$300.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$110.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$70.00[/TD]

[TD="align: right"]$40.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$100.00[/TD]

[TD="align: right"]$130.00[/TD]

[TD="align: right"]$360.00[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]$440.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]$620.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]$1,360.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet76​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=SUMIF(A$2:E$9,A13,B$2:F$9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down for other Companies.
Click to expand...

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.
 
Upvote 0
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
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
Sheet76
Cell Formulas
RangeFormula
B13=SUMIF(A$2:E$9,A13,B$2:F$9)
 
Upvote 0
jtakw said:
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
ChubbEntropyWard
ChubbEntropy
Entropy
ChubbWard
ChubbEntropyWard
Entropy
ChubbEntropyWard
CompanyTotal Cost
Chubb
Entropy
Ward

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]$30.00[/TD]

[TD="align: right"]$60.00[/TD]

[TD="align: right"]$220.00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$40.00[/TD]

[TD="align: right"]$70.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$80.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$60.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$240.00[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$70.00[/TD]

[TD="align: right"]$100.00[/TD]

[TD="align: right"]$300.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$110.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$100.00[/TD]

[TD="align: right"]$130.00[/TD]

[TD="align: right"]$360.00[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]$300.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]$550.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]$1,120.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet76​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=SUMIF(A$2:E$9,A13,B$2:F$9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Your solution is not exactly what I am looking for. I am looking more for an array formula. Thanks for trying.
 
Upvote 0
You're Not explaining what you want, good luck.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Merge QTY & AMOUNT for each part of item for each range
Replies
2
Views
65
Amer Omar
A
M
  • Solved
Add item in first digits with create space after it based on sheet name
Replies
8
Views
111
kvsrinivasamurthy
K
A
  • Question Question
create report for 40000 rows to calculation stock across sheet based on sheet name in cell value
Replies
1
Views
349
Amer Omar
A
M
  • Question Question
Match combobox name on userform with shee name and fill empty cell
Replies
0
Views
247
Maklil
M
K
  • Question Question
modification code to calculation stock on userform
Replies
0
Views
376
KalilMe
K

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top