# Thread: Issue with a Formula Thanks: 0 Likes: 0

1. ## Issue with a Formula

Hi guys, I need a formula with a sum for data.

Example, I halve these data:

 PE - PESCARA - CAFFE
 3030
 TAX
 PE - PESCARA - CAFFE
 3636
 TAX
 PE - PESCARA - CAFFE
 1212
 TAX
 PE - PESCARA - Sub34
 52672,95
 PE - PESCARA - Sub35
 53389,13
 PE - PESCARA - Sub36
 42281,33
 PE - PESCARA - Sub37
 51956,1
 PE - PESCARA - Sub38
 989,63
 Legal Expense
 PE - PESCARA - Sub39
 989,64
 Legal Expense
 PE - PESCARA - Sub40
 989,64
 Legal Expense
 PE - PESCARA - Sub41
 989,63
 Legal Expense
 CH - CHIETI - Sub35
 989,65
 Legal Expense
 CH - CHIETI - Sub39
 989,64
 Legal Expense
 CH - CHIETI - Sub40
 989,64
 Legal Expense
 CH - CHIETI - Sub41
 989,64
 Legal Expense
 CH - CHIETI - Sub42
 989,62
 Legal Expense
 CH - CHIETI - Sub43
 989,62
 Legal Expense
 CH - CHIETI - Sub44
 989,64
 Legal Expense

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

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,

2. ## Re: Issue with a Formula

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

3. ## Re: Issue with a Formula

Originally Posted by James006
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..

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

 7878 200300 3959
With Blank Cells if the data is repeated.

Thank you very very much!