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  Reply With Quote

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  Reply With Quote

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))  Reply With Quote

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)  Reply With Quote

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!  Reply With Quote

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?  Reply With Quote

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.

It should have read...
=SUMIF(\$A\$2:\$A\$9,C2,\$B\$2:\$B\$9)  Reply With Quote

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  Reply With Quote

9. Re: need help for formula

Thats perfect, works perffect. Thank you very much  Reply With Quote

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
•