Issue with a Formula

richard1234567891011

Board Regular
Joined
Feb 21, 2018
Messages
91
Hi guys, I need a formula with a sum for data.

Example, I halve these data:


PE - PESCARA - CAFFE

<tbody>
</tbody>
3030

<tbody>
</tbody>
TAX

<tbody>
</tbody>
PE - PESCARA - CAFFE

<tbody>
</tbody>
3636

<tbody>
</tbody>
TAX

<tbody>
</tbody>
PE - PESCARA - CAFFE

<tbody>
</tbody>
1212

<tbody>
</tbody>
TAX

<tbody>
</tbody>
PE - PESCARA - Sub34

<tbody>
</tbody>
52672,95

<tbody>
</tbody>
Buy

<tbody>
</tbody>
PE - PESCARA - Sub35

<tbody>
</tbody>
53389,13

<tbody>
</tbody>
Buy

<tbody>
</tbody>
PE - PESCARA - Sub36

<tbody>
</tbody>
42281,33

<tbody>
</tbody>
Buy

<tbody>
</tbody>
PE - PESCARA - Sub37

<tbody>
</tbody>
51956,1

<tbody>
</tbody>
Buy

<tbody>
</tbody>
PE - PESCARA - Sub38

<tbody>
</tbody>
989,63

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
PE - PESCARA - Sub39

<tbody>
</tbody>
989,64

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
PE - PESCARA - Sub40

<tbody>
</tbody>
989,64

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
PE - PESCARA - Sub41

<tbody>
</tbody>
989,63

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
CH - CHIETI - Sub35

<tbody>
</tbody>
989,65

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
CH - CHIETI - Sub39

<tbody>
</tbody>
989,64

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
CH - CHIETI - Sub40

<tbody>
</tbody>
989,64

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
CH - CHIETI - Sub41

<tbody>
</tbody>
989,64

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
CH - CHIETI - Sub42

<tbody>
</tbody>
989,62

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
CH - CHIETI - Sub43

<tbody>
</tbody>
989,62

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>
CH - CHIETI - Sub44

<tbody>
</tbody>
989,64

<tbody>
</tbody>
Legal Expense

<tbody>
</tbody>

<tbody>
</tbody>


II need a formula to sum all the same tipe of cost x same/similar cost allocator.
Example

Tax: sum of 3030, 3636, 1212 as result (because all are taxes and all are under
PE - PESCARA - CAFFE

<tbody>
</tbody>

Buy: sum of 52672,95 + 53389,13 + 42281,33 + 51956,1 because are all under buy and all are in the same cost allocator, even if it is slightly different.

Thank you Guys,
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

With your data in A2:C19, you could test following formula in cell D2

Code:
=SUMPRODUCT((LEFT($A$2:$A$19,11)=LEFT(A2,11))*($C$2:$C$19=C2)*($B$2:$B$19))

Hope this will help
 

richard1234567891011

Board Regular
Joined
Feb 21, 2018
Messages
91
Hello,

With your data in A2:C19, you could test following formula in cell D2

Code:
=SUMPRODUCT((LEFT($A$2:$A$19,11)=LEFT(A2,11))*($C$2:$C$19=C2)*($B$2:$B$19))

Hope this will help

Yes perfect, but is there a way in order to not repeat data?

7878
7878
7878
200300
200300
200300
200300
3959
3959

Etc..

<colgroup><col width="87" style="width: 65pt;"></colgroup><tbody>
</tbody>

Instead of this result, is it possible to have only the data not repeated? So, in this way:

7878


200300



3959

<tbody>
</tbody>
With Blank Cells if the data is repeated.

Thank you very very much!
 

Forum statistics

Threads
1,148,108
Messages
5,744,876
Members
423,907
Latest member
zerocool88

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
Top