Sumif problem

r1998

Board Regular
Joined
Sep 9, 2018
Messages
106
Dear friends and respected seniors :)
I have this data starting from cell a1 to d8 :biggrin:
Code:
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
Code:
=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 :biggrin:
Awaiting your replies.
Thank you.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Book1
ABCD
1A25
2A312
3B6-3
4B4
5B2
6C1
7C-6
8C2
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))),"")
 
Upvote 0
wow, that worked perfectly :biggrin: thank u Aladin Akyurek (y)
the formula is extremely complex, i will require many days of studying just to understand this :biggrin: but its just brilliant, Thank you (y)
 
Upvote 0

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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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