Issue with a formula (sum with conditions)

[richard1234567891011]

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: [TABLE="width: 128"]
<colgroup><col width="128" style="width: 96pt;"></colgroup><tbody>[TR]
[TD="class: xl69, width: 128, align: center"] € 1.762.382,50 [/TD]
[/TR]
</tbody>[/TABLE]
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.


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

 

[anglais428]

=SUMIFS($A$2:$A$142,$C$2:$C$142,"CAPEX",$B$2:$B$142,"*BG - ALMENNO - *") gives 1.762.382,50 [TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl63, width: 93"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[richard1234567891011]

=SUMIFS($A$2:$A$142,$C$2:$C$142,"CAPEX",$B$2:$B$142,"*BG - ALMENNO - *") gives 1.762.382,50 [TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl63, width: 93"][/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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.

 

[anglais428]

You're welcome.