Results 1 to 7 of 7
Thread: Conditional Sum (2000 SP3)

20080806, 00:46 #1
 Join Date
 Aug 2001
 Location
 Confoederatio Helvetica
 Posts
 602
 Thanks
 0
 Thanked 0 Times in 0 Posts
Conditional Sum (2000 SP3)
I've done some searching and found a number of similar posts  but not one that matches what I'm looking for.
I've got over 900 rows x 10 columns of data. I want to sum values in one column based on matching information in another column. The matching data could be 1 or more (as many as 12). In the table below the first two columns is the data I have and I want to calculate the value in the third column. i.e. AA 100 + 200 = 300, AB 110 = 110, AC 150 + 95 + 100 = 345, AD 200 = 200. I've tried IFSUM, but that doesn't seem to work. Any other ideas? <img src=/S/please.gif border=0 alt=please width=31 height=23>
<table border=1><td>AA</td><td>100</td><td>AA</td><td>200</td><td>300</td></tr><td>AB</td><td>110</td><td>110</td></tr><td>AC</td><td>150</td></tr><td>AC</td><td>95</td></tr><td>AC</td><td>100</td><td>345</td></tr><td>AD</td><td>200</td><td>200</td></tr></table>

20080806, 01:16 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Conditional Sum (2000 SP3)
In C1:
=IF(COUNTIF(A$1:A1,A1)<>COUNTIF(A:A,A1), "",SUMIF(A:A,A1,B:)
Copy down the column...
Steve

20080806, 01:29 #3
 Join Date
 Aug 2001
 Location
 Confoederatio Helvetica
 Posts
 602
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Sum (2000 SP3)
Steve
Thanks  the conditional data is in column A but the numbers I need summing are in column E so I changed your formula to
=IF(COUNTIF(A$1:A1,A1)<>COUNTIF(A:A,A1), "",SUMIF(A:A,A1,E:E))
and copied down the column. However the results are mostly 0 (zero) I do get some results but they don't appear to be related to the values in E.
What am I missing?

20080806, 01:35 #4
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Conditional Sum (2000 SP3)
Assume your range of data, "AA" started at A2
C2, enter the formula and copied down :
=IF(A2<>A3,SUMIF(A:A,A2,B:,"")

20080806, 01:40 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Conditional Sum (2000 SP3)
Are the values truly numbers or are they text that just look like numbers? Remove any cell alignment, numbers align to right and text to the left...
Otherwise, could you post an example file with the problem?
Steve

20080806, 01:42 #6
 Join Date
 Aug 2001
 Location
 Confoederatio Helvetica
 Posts
 602
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Sum (2000 SP3)
Here's an example

20080806, 01:50 #7
 Join Date
 Aug 2001
 Location
 Confoederatio Helvetica
 Posts
 602
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Sum (2000 SP3)
Arghh!!!! You are right  I forgot to Change A1 to A8  works perfectly now!!!
Thanks to both Steve and you!!!!