Results 1 to 6 of 6

20070406, 06:32 #1
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
count/sum array formula (excel2003)
hi all,
what I have is different names in column a, in column b are different types for each name. what I want to do is treat three types gp,gpk,gpkf as being one type and i want to get their count and their value sum in column g2 & h2 for each name. can one name be defined for these three types and then do the count and the sum operations?
regards,
dubdubTIA
dubdub

20070406, 09:01 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: count/sum array formula (excel2003)
An alternate approach if you want to check that the first 2 letters in column B are "gp" is to use:
=SUM(IF(($A$2:$A$15=$F5)*(LEFT($B$2:$B$15,2)="gp") ,1))
=SUM(IF(($A$2:$A$15=$F5)*(LEFT($B$2:$B$15,2)="gp") ,$C$2:$C$15))
Steve

20070406, 09:07 #3
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: count/sum array formula (excel2003)
many thanks steve.
one minor correction to the first formula, one more $ sign for the B range
=SUM(IF(($A$2:$A$15=$F2)*(($B$2:$B$15="gp")+($B$2: $B$15="gpk")+($B$2:$B$15="gpkf")),1))
dubdubTIA
dubdub

20070406, 09:15 #4
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: count/sum array formula (excel2003)
many thanks to you Steve.
Same results and less headache to track and to enter these type names.
dubdubTIA
dubdub

20070406, 09:27 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: count/sum array formula (excel2003)
<P ID="edit" class=small>(Edited by sdckapr on 06Apr07 05:27. Edited to allow copying and add forgotten dollar sign)</P>You were very close (I also cleaned up some unneccessary parentheses)
In G2 the array (cntrlshiftenter):
=Sum(IF(($A$2:$A$15=$F2)*(($B$2:$B$15="gp")+($B$2: $B$15="gpk")+($B$2:$B$15="gpkf")),1))
You can't "count" the items in A since A has no numbers, you can't use "Counta" since both 1s and zeroes will be counted.
In H2:
=SUM(IF(($A$2:$A$15=$F2)*(($B$2:$B$15="gp")+($B$2: $B$15="gpk")+($B$2:$B$15="gpkf")),$C$2:$C$15))
You want to sum the values in column C, not the values in Col A...
Steve

20070406, 09:28 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: count/sum array formula (excel2003)
<hr>one minor correction to the first formula, one more $ sign for the B range<hr>
<img src=/S/blush.gif border=0 alt=blush width=15 height=15> that is why I get for editing the text afterwards.... I have corrected my post
Steve