# Issue with a formula (sum with conditions)

#### richard1234567891011

##### Board Regular
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 interna Descrizione CdC Classificazione -1.600,00 PI - PISA - CASTE RICAVI -1.600,00 PI - PISA - CASTE RICAVI -1.600,00 PI - PISA - CASTE RICAVI -2.000,00 PI - PISA - CASTE RICAVI -2.000,00 PI - PISA - CASTE RICAVI -240,00 PI - PISA - CASTE ASA -82,00 PI - PISA - CASTE ASA -32.511,16 BG - ALMENNO - Sub58 CAPEX -32.687,64 BG - ALMENNO - Sub57 CAPEX -32.736,67 BG - ALMENNO - Sub59 CAPEX -13.794,69 BG - ALMENNO - Sub10 CAPEX -12.843,67 BG - ALMENNO - Sub12 CAPEX -16.245,77 BG - ALMENNO - Sub26 CAPEX -17.037,07 BG - ALMENNO - Sub09 CAPEX -17.009,41 BG - ALMENNO - Sub28 CAPEX -15.281,71 BG - ALMENNO - Sub24 CAPEX -3.702,01 BG - ALMENNO - Sub64 CAPEX -3.702,01 BG - ALMENNO - Sub65 CAPEX -3.702,01 BG - ALMENNO - Sub66 CAPEX -5.090,27 BG - ALMENNO - Sub54 CAPEX -4.858,89 BG - ALMENNO - Sub50 CAPEX -4.858,89 BG - ALMENNO - Sub53 CAPEX -3.271,52 BG - ALMENNO - Sub42 CAPEX -4.803,12 BG - ALMENNO - Sub51 CAPEX -3.648,39 BG - ALMENNO - Sub44 CAPEX -4.644,47 BG - ALMENNO - Sub58 CAPEX -4.669,68 BG - ALMENNO - Sub57 CAPEX -4.676,69 BG - ALMENNO - Sub59 CAPEX -1.970,68 BG - ALMENNO - Sub10 CAPEX -1.834,82 BG - ALMENNO - Sub12 CAPEX -2.320,83 BG - ALMENNO - Sub26 CAPEX -2.438,48 BG - ALMENNO - Sub09 CAPEX -2.438,48 BG - ALMENNO - Sub28 CAPEX -2.191,97 BG - ALMENNO - Sub24 CAPEX -16.300,00 CH - CHIETI - DE LEL ASA 2.757,00 CH - CHIETI - DE LEL ASA 812,00 BG - ALMENNO - Q. RO ASA 3.309,00 CH - CHIETI - DE LEL ASA 4.872,00 BG - ALMENNO - Q. RO ASA 2.613,00 PI - PISA - CASTE ASA 1.103,00 CH - CHIETI - DE LEL ASA 1.624,00 BG - ALMENNO - Q. RO ASA 290,00 PI - PISA - CASTE ASA 989,65 CH - CHIETI - Sub35 CAPEX 989,64 CH - CHIETI - Sub39 CAPEX 989,64 CH - CHIETI - Sub40 CAPEX 989,64 CH - CHIETI - Sub41 CAPEX 989,62 CH - CHIETI - Sub42 CAPEX 989,62 CH - CHIETI - Sub43 CAPEX 989,64 CH - CHIETI - Sub44 CAPEX 780,00 PI - PISA - CASTE CAPEX 82,00 PI - PISA - CASTE ASA 189,03 BG - ALMENNO - Sub58 CAPEX 190,05 BG - ALMENNO - Sub57 CAPEX 190,34 BG - ALMENNO - Sub59 CAPEX 80,21 BG - ALMENNO - Sub10 CAPEX 13.077,34 BG - ALMENNO - Sub44 CAPEX 3.168,00 BG - ALMENNO - Sub44 CAPEX 3.168,00 BG - ALMENNO - Sub44 CAPEX 3.168,00 BG - ALMENNO - Sub44 CAPEX 4.356,00 BG - ALMENNO - Sub44 CAPEX 4.158,00 BG - ALMENNO - Sub44 CAPEX 4.158,00 BG - ALMENNO - Sub44 CAPEX 2.799,61 BG - ALMENNO - Sub44 CAPEX 4.110,27 BG - ALMENNO - Sub44 CAPEX 3.122,11 BG - ALMENNO - Sub44 CAPEX 480,00 PI - PISA - CASTE ASA 37.782,45 CH - CHIETI - Sub39 CAPEX 29.382,35 CH - CHIETI - Sub40 CAPEX 54.285,53 CH - CHIETI - Sub35 CAPEX 14.332,95 CH - CHIETI - Sub41 CAPEX 9.077,40 CH - CHIETI - Sub42 CAPEX 9.077,40 CH - CHIETI - Sub43 CAPEX 14.332,95 CH - CHIETI - Sub44 CAPEX 278.214,47 BG - ALMENNO - Sub58 CAPEX 279.724,68 BG - ALMENNO - Sub57 CAPEX 280.144,19 BG - ALMENNO - Sub59 CAPEX 118.048,18 BG - ALMENNO - Sub10 CAPEX 109.909,82 BG - ALMENNO - Sub12 CAPEX 139.023,33 BG - ALMENNO - Sub26 CAPEX 145.794,90 BG - ALMENNO - Sub09 CAPEX 145.558,24 BG - ALMENNO - Sub28 CAPEX 130.773,42 BG - ALMENNO - Sub24 CAPEX 31.680,00 BG - ALMENNO - Sub64 CAPEX 31.680,00 BG - ALMENNO - Sub65 CAPEX 31.680,00 BG - ALMENNO - Sub66 CAPEX 43.560,00 BG - ALMENNO - Sub54 CAPEX 41.580,00 BG - ALMENNO - Sub50 CAPEX 41.580,00 BG - ALMENNO - Sub53 CAPEX 27.996,08 BG - ALMENNO - Sub42 CAPEX 41.102,74 BG - ALMENNO - Sub51 CAPEX 31.221,05 BG - ALMENNO - Sub44 CAPEX 290.000,00 PI - PISA - CASTE CAPEX 916,39 BG - ALMENNO - Sub58 CAPEX 921,36 BG - ALMENNO - Sub57 CAPEX 922,75 BG - ALMENNO - Sub59 CAPEX 388,83 BG - ALMENNO - Sub10 CAPEX 362,02 BG - ALMENNO - Sub12 CAPEX 457,92 BG - ALMENNO - Sub26 CAPEX 480,22 BG - ALMENNO - Sub09 CAPEX 479,44 BG - ALMENNO - Sub28 CAPEX 430,74 BG - ALMENNO - Sub24 CAPEX 104,35 BG - ALMENNO - Sub64 CAPEX 104,35 BG - ALMENNO - Sub65 CAPEX 104,35 BG - ALMENNO - Sub66 CAPEX 143,48 BG - ALMENNO - Sub54 CAPEX 136,96 BG - ALMENNO - Sub50 CAPEX 136,96 BG - ALMENNO - Sub53 CAPEX 92,21 BG - ALMENNO - Sub42 CAPEX 135,39 BG - ALMENNO - Sub51 CAPEX 102,84 BG - ALMENNO - Sub44 CAPEX 9.197,49 BG - ALMENNO - Q. RO CAPEX 2.240,08 CH - CHIETI - DE LEL CAPEX 1.406,25 BG - ALMENNO - Q. RO CAPEX 4.302,69 BG - ALMENNO - Q. RO ASA 4.049,94 CH - CHIETI - DE LEL ASA 821,08 BG - ALMENNO - Q. RO ASA 282,00 CH - CHIETI - DE LEL ASA 1.249,04 BG - ALMENNO - Q. RO ASA 859,22 BG - ALMENNO - Sub58 CAPEX 863,88 BG - ALMENNO - Sub57 CAPEX 865,18 BG - ALMENNO - Sub59 CAPEX 364,57 BG - ALMENNO - Sub10 CAPEX 339,44 BG - ALMENNO - Sub12 CAPEX 429,35 BG - ALMENNO - Sub26 CAPEX 450,26 BG - ALMENNO - Sub09 CAPEX 449,53 BG - ALMENNO - Sub28 CAPEX 403,87 BG - ALMENNO - Sub24 CAPEX 97,84 BG - ALMENNO - Sub64 CAPEX 2.568,80 PI - PISA - CASTE ASA 504,81 CH - CHIETI - DE LEL ASA 5.847,81 BG - ALMENNO - Q. RO ASA 1.602,14 BG - ALMENNO - Q. RO ASA 5.847,81 BG - ALMENNO - Q. RO ASA 3.898,54 BG - ALMENNO - Q. RO ASA 1.660,00 PI - PISA - CASTE ASA 580,69 CH - CHIETI - DE LEL ASA 6.000,00 BG - ALMENNO - Q. RO ASA 500,00 BG - ALMENNO - Q. RO ASA 500,00 PI - PISA - CASTE ASA 49,00 PI - PISA - CASTE ASA

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### anglais428

##### Well-known Member
=SUMIFS(\$A\$2:\$A\$142,\$C\$2:\$C\$142,"CAPEX",\$B\$2:\$B\$142,"*BG - ALMENNO - *") gives 1.762.382,50

<tbody>
</tbody>

#### richard1234567891011

##### Board Regular
=SUMIFS(\$A\$2:\$A\$142,\$C\$2:\$C\$142,"CAPEX",\$B\$2:\$B\$142,"*BG - ALMENNO - *") gives 1.762.382,50

<tbody>
</tbody>

Yes, The formula seems nearly perfect, thank you very much. There is only one issue, How can I insert a cell instead of *BG - ALMENNO - *?

#### anglais428

##### Well-known Member
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.

#### richard1234567891011

##### Board Regular
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.

I tested your formula many times. It is perfect. I love it. Thank you very much.

You're welcome.

Replies
2
Views
323

1,148,108
Messages
5,744,878
Members
423,908
Latest member
Getfour

### 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.

### Which adblocker are you using?

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

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