Thread: need help for formula Thanks: 0 Likes: 0

1. need help for formula

Hello all i got 2 columns A and B , with random values in them.

I need in column C to put all of the values ​​that appear in the column A with no repeat, and in column D to put the sum of all values in column B which correspond to the values ​​of a column A. I need that to be formula conection, not advanced filter.

example

A B C D
1 3 1 4
2 5 2 12
3 6 3 8
2 7 4 10
1 1
4 6
3 2
4 4

Thank you this is very important to me, because i always have problem with advanced filter

2. Re: need help for formula

Hi Chobanne,

Why is the bottom value in column C "4"? should it not be blank because 2 is already present in the 2nd row of column A?

I don't follow the logic in column D. Should D equal A plus B? It's that in the first row but not the other rows.

Poiu

3. Re: need help for formula

I dont know what you understand, but in C i want to show every value that exist in column A (thats 1,2,3 and 4) and in D i want to get sum of values in B which are next to this values in column A (thats for 1(3+1=4),2(5+7=12),3(6+2=8) and 4(6+4=10))

4. Re: need help for formula

In C2, copied down...
=IFERROR(INDEX(\$A\$2:\$A\$9,MATCH(0,INDEX(COUNTIF(\$C\$1:C1,\$A\$2:\$A\$9),0,0),0)),"")

Then in D2, copied down...
=SUMIF(\$A\$2:\$A\$9,E2,\$B\$2:\$B\$9)

5. Re: need help for formula

OK I understand column C now, but still now column D - please could you retry?

edit: ignore me - FDibbins beat me to it!

6. Re: need help for formula

Originally Posted by FDibbins
In C2, copied down...
=IFERROR(INDEX(\$A\$2:\$A\$9,MATCH(0,INDEX(COUNTIF(\$C\$1:C1,\$A\$2:\$A\$9),0,0),0)),"")

Then in D2, copied down...
=SUMIF(\$A\$2:\$A\$9,E2,\$B\$2:\$B\$9)
first formula is good but second dont, whats E2?

7. Re: need help for formula

Apologies, I had thise 2 formulas in E and F, so that I vcould keep your original data to compare with.

=SUMIF(\$A\$2:\$A\$9,C2,\$B\$2:\$B\$9)

8. Re: need help for formula

Hello,

Here is a possible solution. I found this amazing formula for unique values online at Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital Help - Microsoft Excel resource

Note that the formula uses a few named ranges for simplicity, make sure you add those, If you want make them dynamic with this formulas:

Code:
`List=OFFSET(Sheet2!\$A\$1,1,0,COUNTA(Sheet2!\$A:\$A)-1,1)`
Code:
`Values=OFFSET(Sheet2!\$B\$1,1,0,COUNTA(Sheet2!\$B:\$B)-1,1)`
Also the formula in C1:C9 is an array formula press ctr+Shift+enter to enter it.

Here is the main formula for the unique values:
Code:
`=IF(COUNT(IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""))>(ROW(List)-ROW(List_Start)),INDEX(List,MATCH(SMALL(IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""),(ROW(List)-ROW(List_Start)+1)),IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""),0)),"")`
This is the formula for the calculations:
Code:
`=IF(C2="","",SUMPRODUCT(Values,--(List=C2)))`
I have attached a copy of the work book in case you have questions https://dl.dropboxusercontent.com/u/.../Solution.xlsx

Thanks

9. Re: need help for formula

Thats perfect, works perffect. Thank you very much

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•