Sumif problem

[r1998]

Dear friends and respected seniors
I have this data starting from cell a1 to d8

A	2	5	5
A	3	5	12
B	6	12	-3
B	4	12	
B	2	12	
C	1	-3	
C	-6	-3	
C	2	-3

I am comparing the contents of column A and summing up similar data from column B
a1 and a2 has A, hence 2+3 = 5,
a3, a4 and a5 have B, hence 6+4+2 = 12,
a6, a7 and a8 have C, hence 1+(-6)+2 = -3, and so on
in c1 I have used this formula and dragged down till c8

=SUMIF($A$1:$A$8,A1,$B$1:$B$8)

the answers are correct, but that is not the output that i want
In column D, i have entered the required output manually
Can anyone please kindly help me with this problem
Awaiting your replies.
Thank you.

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	A	2		5
2	A	3		12
3	B	6		-3
4	B	4
5	B	2
6	C	1
7	C	-6
8	C	2
Sheet1

In D1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(SUMIFS($B$1:$B$8,$A$1:$A$8,IF(FREQUENCY(IF($A$1:$A$8<>"",MATCH($A$1:$A$8,$A$1:$A$8,0)),ROW($A$1:$B$8)-ROW(INDEX($A$1:$B$8,1,1))+1),$A$1:$A$8)),SMALL(IF(FREQUENCY(IF($A$1:$A$8<>"",MATCH($A$1:$A$8,$A$1:$A$8,0)),ROW($A$1:$B$8)-ROW(INDEX($A$1:$B$8,1,1))+1),ROW($A$1:$B$8)-ROW(INDEX($A$1:$B$8,1,1))+1),ROWS($1:1))),"")

 

[r1998]

wow, that worked perfectly thank u Aladin Akyurek
the formula is extremely complex, i will require many days of studying just to understand this but its just brilliant, Thank you

 

[Aladin Akyurek]

r1998

You are welcome. Here some links:

https://www.mrexcel.com/forum/excel-questions/633564-counting-duplicates-using-sum-if-frequency-match-2.html#post3156949

https://www.mrexcel.com/forum/excel-questions/605567-counting-summing-based-2-columns.html#post3000815


I must have also a link somewhere on how the exdended FREQUENCY formula creates a list of unique items.

 

[r1998]

Thanks Aladin Akyurek
I think this might be that link
https://www.mrexcel.com/forum/excel-questions/73195-list-frequency-occurrences-text-list.html
https://www.mrexcel.com/forum/excel-questions/280112-count-unique-values.html

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-formulas-and-functions/1246721-sumif-array-problem.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[r1998]

Sorry Macropod
I posted in excelforum.com forum too while waiting for an answer here, then when i got an answer here i was very happy n forgot to mention here that i had posted the same question in another forum
this is the link of my post there
https://www.excelforum.com/excel-formulas-and-functions/1246721-sumif-array-problem.html

 

[Aladin Akyurek]

Thanks Aladin Akyurek
I think this might be that link
https://www.mrexcel.com/forum/excel-questions/73195-list-frequency-occurrences-text-list.html
https://www.mrexcel.com/forum/excel-questions/280112-count-unique-values.html

These are not related to the FREQUENCY bits used in post #2 .

The bit

IF(FREQUENCY(IF($A$1:$A$8<>"",MATCH($A$1:$A$8,$A$1:$A$8,0)),ROW($A$1:$B$8)-ROW(INDEX($A$1:$B$8,1,1))+1),$A$1:$A$8)

delivers the items from $A$1:$A$8 for which if the FREQUENCY(…) returns a corresponding non-zero count (i.e. >0).

The bit

SMALL(IF(FREQUENCY(IF($A$1:$A$8<>"",MATCH($A$1:$A$8,$A$1:$A$8,0)),ROW($A$1:$B$8)-ROW(INDEX($A$1:$B$8,1,1))+1),ROW($A$1:$B$8)-ROW(INDEX($A$1:$B$8,1,1))+1)

returns row numbers (bolded part) corresponding to $A$1:$A$8 for which if the FREQUENCY(…) returns a corresponding non-zero count (i.e. >0).

Hope this helps.

 

[r1998]

Thanks Aladin Akyurek