Issue with a formula (sum with conditions)

R

richard1234567891011

Board Regular
Joined
Feb 21, 2018
Messages
91
Hi guys,

We are talking about buildings, which is irrelevant, but it may be relevant only for your understanding of the issue.

I have an issue with this formula.

I need a formula that is able to sum the values based on particular conditions:

1) Based on the central cost
2) Based on CAPEX or ASA
3) Considering the Total Central cost

So this is the issue:

If we take for example BG - Almenno, we have many central costs that are the subs, but we also have the total central cost that is [COLOR=rgba(0, 0, 0, 0.847059)]BG - ALMENNO - Q. RO [/COLOR](All the things underlined in yellow are different total central costs)

So in this case the formula has to sum all the values in Almenno, central costs and total central costs, but only the costs under CAPEX, so the sum is:
€ 1.762.382,50

<colgroup><col width="128" style="width: 96pt;"></colgroup><tbody>
</tbody>
In any case I need to change building (this is a simplified file) but I need a Voolookup or an INDEX MATCH to change the building and receive the sum of the CAPEX.
After I need the same formula but for the ASA
The things in blue are simpler because the concept is the same but you have only the Total central Cost without no subs.

I hope it is clear, thank you guys.


Importo in divisa internaDescrizione CdCClassificazione
-1.600,00PI - PISA - CASTERICAVI
-1.600,00PI - PISA - CASTERICAVI
-1.600,00PI - PISA - CASTERICAVI
-2.000,00PI - PISA - CASTERICAVI
-2.000,00PI - PISA - CASTERICAVI
-240,00PI - PISA - CASTEASA
-82,00PI - PISA - CASTEASA
-32.511,16BG - ALMENNO - Sub58CAPEX
-32.687,64BG - ALMENNO - Sub57CAPEX
-32.736,67BG - ALMENNO - Sub59CAPEX
-13.794,69BG - ALMENNO - Sub10CAPEX
-12.843,67BG - ALMENNO - Sub12CAPEX
-16.245,77BG - ALMENNO - Sub26CAPEX
-17.037,07BG - ALMENNO - Sub09CAPEX
-17.009,41BG - ALMENNO - Sub28CAPEX
-15.281,71BG - ALMENNO - Sub24CAPEX
-3.702,01BG - ALMENNO - Sub64CAPEX
-3.702,01BG - ALMENNO - Sub65CAPEX
-3.702,01BG - ALMENNO - Sub66CAPEX
-5.090,27BG - ALMENNO - Sub54CAPEX
-4.858,89BG - ALMENNO - Sub50CAPEX
-4.858,89BG - ALMENNO - Sub53CAPEX
-3.271,52BG - ALMENNO - Sub42CAPEX
-4.803,12BG - ALMENNO - Sub51CAPEX
-3.648,39BG - ALMENNO - Sub44CAPEX
-4.644,47BG - ALMENNO - Sub58CAPEX
-4.669,68BG - ALMENNO - Sub57CAPEX
-4.676,69BG - ALMENNO - Sub59CAPEX
-1.970,68BG - ALMENNO - Sub10CAPEX
-1.834,82BG - ALMENNO - Sub12CAPEX
-2.320,83BG - ALMENNO - Sub26CAPEX
-2.438,48BG - ALMENNO - Sub09CAPEX
-2.438,48BG - ALMENNO - Sub28CAPEX
-2.191,97BG - ALMENNO - Sub24CAPEX
-16.300,00CH - CHIETI - DE LELASA
2.757,00CH - CHIETI - DE LELASA
812,00BG - ALMENNO - Q. ROASA
3.309,00CH - CHIETI - DE LELASA
4.872,00BG - ALMENNO - Q. ROASA
2.613,00PI - PISA - CASTEASA
1.103,00CH - CHIETI - DE LELASA
1.624,00BG - ALMENNO - Q. ROASA
290,00PI - PISA - CASTEASA
989,65CH - CHIETI - Sub35CAPEX
989,64CH - CHIETI - Sub39CAPEX
989,64CH - CHIETI - Sub40CAPEX
989,64CH - CHIETI - Sub41CAPEX
989,62CH - CHIETI - Sub42CAPEX
989,62CH - CHIETI - Sub43CAPEX
989,64CH - CHIETI - Sub44CAPEX
780,00PI - PISA - CASTECAPEX
82,00PI - PISA - CASTEASA
189,03BG - ALMENNO - Sub58CAPEX
190,05BG - ALMENNO - Sub57CAPEX
190,34BG - ALMENNO - Sub59CAPEX
80,21BG - ALMENNO - Sub10CAPEX
13.077,34BG - ALMENNO - Sub44CAPEX
3.168,00BG - ALMENNO - Sub44CAPEX
3.168,00BG - ALMENNO - Sub44CAPEX
3.168,00BG - ALMENNO - Sub44CAPEX
4.356,00BG - ALMENNO - Sub44CAPEX
4.158,00BG - ALMENNO - Sub44CAPEX
4.158,00BG - ALMENNO - Sub44CAPEX
2.799,61BG - ALMENNO - Sub44CAPEX
4.110,27BG - ALMENNO - Sub44CAPEX
3.122,11BG - ALMENNO - Sub44CAPEX
480,00PI - PISA - CASTEASA
37.782,45CH - CHIETI - Sub39CAPEX
29.382,35CH - CHIETI - Sub40CAPEX
54.285,53CH - CHIETI - Sub35CAPEX
14.332,95CH - CHIETI - Sub41CAPEX
9.077,40CH - CHIETI - Sub42CAPEX
9.077,40CH - CHIETI - Sub43CAPEX
14.332,95CH - CHIETI - Sub44CAPEX
278.214,47BG - ALMENNO - Sub58CAPEX
279.724,68BG - ALMENNO - Sub57CAPEX
280.144,19BG - ALMENNO - Sub59CAPEX
118.048,18BG - ALMENNO - Sub10CAPEX
109.909,82BG - ALMENNO - Sub12CAPEX
139.023,33BG - ALMENNO - Sub26CAPEX
145.794,90BG - ALMENNO - Sub09CAPEX
145.558,24BG - ALMENNO - Sub28CAPEX
130.773,42BG - ALMENNO - Sub24CAPEX
31.680,00BG - ALMENNO - Sub64CAPEX
31.680,00BG - ALMENNO - Sub65CAPEX
31.680,00BG - ALMENNO - Sub66CAPEX
43.560,00BG - ALMENNO - Sub54CAPEX
41.580,00BG - ALMENNO - Sub50CAPEX
41.580,00BG - ALMENNO - Sub53CAPEX
27.996,08BG - ALMENNO - Sub42CAPEX
41.102,74BG - ALMENNO - Sub51CAPEX
31.221,05BG - ALMENNO - Sub44CAPEX
290.000,00PI - PISA - CASTECAPEX
916,39BG - ALMENNO - Sub58CAPEX
921,36BG - ALMENNO - Sub57CAPEX
922,75BG - ALMENNO - Sub59CAPEX
388,83BG - ALMENNO - Sub10CAPEX
362,02BG - ALMENNO - Sub12CAPEX
457,92BG - ALMENNO - Sub26CAPEX
480,22BG - ALMENNO - Sub09CAPEX
479,44BG - ALMENNO - Sub28CAPEX
430,74BG - ALMENNO - Sub24CAPEX
104,35BG - ALMENNO - Sub64CAPEX
104,35BG - ALMENNO - Sub65CAPEX
104,35BG - ALMENNO - Sub66CAPEX
143,48BG - ALMENNO - Sub54CAPEX
136,96BG - ALMENNO - Sub50CAPEX
136,96BG - ALMENNO - Sub53CAPEX
92,21BG - ALMENNO - Sub42CAPEX
135,39BG - ALMENNO - Sub51CAPEX
102,84BG - ALMENNO - Sub44CAPEX
9.197,49BG - ALMENNO - Q. ROCAPEX
2.240,08CH - CHIETI - DE LELCAPEX
1.406,25BG - ALMENNO - Q. ROCAPEX
4.302,69BG - ALMENNO - Q. ROASA
4.049,94CH - CHIETI - DE LELASA
821,08BG - ALMENNO - Q. ROASA
282,00CH - CHIETI - DE LELASA
1.249,04BG - ALMENNO - Q. ROASA
859,22BG - ALMENNO - Sub58CAPEX
863,88BG - ALMENNO - Sub57CAPEX
865,18BG - ALMENNO - Sub59CAPEX
364,57BG - ALMENNO - Sub10CAPEX
339,44BG - ALMENNO - Sub12CAPEX
429,35BG - ALMENNO - Sub26CAPEX
450,26BG - ALMENNO - Sub09CAPEX
449,53BG - ALMENNO - Sub28CAPEX
403,87BG - ALMENNO - Sub24CAPEX
97,84BG - ALMENNO - Sub64CAPEX
2.568,80PI - PISA - CASTEASA
504,81CH - CHIETI - DE LELASA
5.847,81BG - ALMENNO - Q. ROASA
1.602,14BG - ALMENNO - Q. ROASA
5.847,81BG - ALMENNO - Q. ROASA
3.898,54BG - ALMENNO - Q. ROASA
1.660,00PI - PISA - CASTEASA
580,69CH - CHIETI - DE LELASA
6.000,00BG - ALMENNO - Q. ROASA
500,00BG - ALMENNO - Q. ROASA
500,00PI - PISA - CASTEASA
49,00PI - PISA - CASTEASA

<colgroup><col width="111" style="width: 83pt;"><col width="196" style="width: 147pt;"><col width="101" style="width: 76pt;"></colgroup><tbody>
</tbody>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
For example:
=SUMIFS($A$2:$A$142,$C$2:$C$142,"CAPEX",$B$2:$B$142,"*"&G2&"*")
Where cell G2 contains the text BG - ALMENNO -
The asterix are wildcard operators that help provide a partial match. If you need an exact match, you can remove these.
 
Upvote 0
anglais428 said:
For example:
=SUMIFS($A$2:$A$142,$C$2:$C$142,"CAPEX",$B$2:$B$142,"*"&G2&"*")
Where cell G2 contains the text BG - ALMENNO -
The asterix are wildcard operators that help provide a partial match. If you need an exact match, you can remove these.
Click to expand...


I tested your formula many times. It is perfect. I love it. Thank you very much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Issue with a Formula
Replies
2
Views
507
richard1234567891011
R

Forum statistics

Threads
1,214,575
Messages
6,120,334
Members
448,956
Latest member
Adamsxl

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