Countif, sumproduct, Index/match Question

  • Thread starter Thread starter Euh
  • Start date Start date
E

Euh

I have data arrange like that (with blanks):


A B C
4.44 1
4.45 2
4.45 2
3.47 2
4.87 3
4.87 3
3.88 4
3.88 4
2.89 5
2.89 5
1.43 5
1.92 6
1.44 7
1.44 7


I want to put in column D the sum of all elements in "A" corresponding
to each
index contained in column "C" (and remove the blanks at the same time)

It would yield the following:

D
row1: 0
row2: 4.45+3.47
row3: 4.87
row4: 3.88
row5: 2.89+1.43
row6: 0
row7: 1.44


I made a huge formula that works, but I'm sure there is a more elegant
way to
do it...any ideas ?

what I've got so far:

+SUM(INDEX($A$2:$A$40,MATCH(ROW()-1,$C$2:$C$40,0)):INDEX($A$2:$A$40,MATCH(ROW()-1,$C$2:$C$40,0)+COUNTIF($C$2:$C$40,ROW()-1)-1))
 
Hi
try the following on a SEPARATE sheet (removing rows with formulas is
not possible).
In cell C1 enter the array formula (entered with CTRL+SHIFT+ENTER)
=INDEX('sheet1'!$C$1:$C$1000,SMALL(IF('sheet1'!$C$1:$C$1000<>"",ROW('sh
eet1'!$C$1:$C$1000)),ROW(1:1)))

in D1 enter
=SUMIF('sheet1'!$B$1:$B$1000,C1,'sheet1'!$A$1:$A$1000)

copy both cells down


--
Regards
Frank Kabel
Frankfurt, Germany

I have data arrange like that (with blanks):


A B C
4.44 1
4.45 2
4.45 2
3.47 2
4.87 3
4.87 3
3.88 4
3.88 4
2.89 5
2.89 5
1.43 5
1.92 6
1.44 7
1.44 7


I want to put in column D the sum of all elements in "A" corresponding
to each
index contained in column "C" (and remove the blanks at the same time)

It would yield the following:

D
row1: 0
row2: 4.45+3.47
row3: 4.87
row4: 3.88
row5: 2.89+1.43
row6: 0
row7: 1.44


I made a huge formula that works, but I'm sure there is a more elegant
way to
do it...any ideas ?

what I've got so far:
Click to expand...
+SUM(INDEX($A$2:$A$40,MATCH(ROW()-1,$C$2:$C$40,0)):INDEX($A$2:$A$40,MAT
CH(ROW()-1,$C$2:$C$40,0)+COUNTIF($C$2:$C$40,ROW()-1)-1))
 
try the following on a SEPARATE sheet (removing rows with formulas is
not possible).
Click to expand...

Sorry, I didnt mean that I want to delete the rows. Simply have no blanks in
column D.
Anyway to perform it without using arrays ?
 
Back
Top