Issue with a Formula

R

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:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #DFF8D6"]PE - PESCARA - CAFFE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]3030[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]TAX[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #DFF8D6"]PE - PESCARA - CAFFE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]3636[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]TAX[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #DFF8D6"]PE - PESCARA - CAFFE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]1212[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]TAX[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #CEFBF5"]PE - PESCARA - Sub34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]52672,95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Buy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #CEFBF5"]PE - PESCARA - Sub35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]53389,13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Buy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #CEFBF5"]PE - PESCARA - Sub36[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]42281,33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Buy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #CEFBF5"]PE - PESCARA - Sub37[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]51956,1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Buy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #CEFBF5"]PE - PESCARA - Sub38[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,63[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #CEFBF5"]PE - PESCARA - Sub39[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,64[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #CEFBF5"]PE - PESCARA - Sub40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,64[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #CEFBF5"]PE - PESCARA - Sub41[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,63[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="width: 139"]CH - CHIETI - Sub35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,65[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="width: 139"]CH - CHIETI - Sub39[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,64[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="width: 139"]CH - CHIETI - Sub40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,64[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="width: 139"]CH - CHIETI - Sub41[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,64[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="width: 139"]CH - CHIETI - Sub42[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,62[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="width: 139"]CH - CHIETI - Sub43[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,62[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 139"]
<tbody>[TR]
[TD="width: 139"]CH - CHIETI - Sub44[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]989,64[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl73, width: 127"]Legal Expense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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 [TABLE="width: 139"]
<tbody>[TR]
[TD="class: xl73, width: 139, bgcolor: #DFF8D6"]PE - PESCARA - CAFFE[/TD]
[/TR]
</tbody>[/TABLE]

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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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
 
Upvote 0
James006 said:
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
Click to expand...

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

[TABLE="width: 87"]
<colgroup><col width="87" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl73, width: 87, align: right"]7878[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]7878[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]7878[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]200300[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]200300[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]200300[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]200300[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]3959[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]3959

Etc..
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl73, width: 87, align: right"]7878[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]

[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]200300[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]


[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 87, align: right"]3959[/TD]
[/TR]
</tbody>[/TABLE]
With Blank Cells if the data is repeated.

Thank you very very much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Issue with a formula (sum with conditions)
Replies
5
Views
710
anglais428
A

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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