Results 1 to 9 of 9

20090206, 15:52 #1
 Join Date
 Jun 2005
 Posts
 391
 Thanks
 3
 Thanked 0 Times in 0 Posts
counting the # of items in a w/s column (xp & 2003)
I would like to get a count of items in a works sheet given two or more criteria. For instance, if a 3 columns has 20 rows and I want to count the number of times col. A and col B have the criterion of the Fruit "Apples" Col A and the color "Red" Col B if the "Quantity" in Col C is greater than 15. I can't quite get it figured out.
Thank you

20090206, 16:11 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: counting the # of items in a w/s column (xp & 2003)
Youcan use a formula like this:
=SUMPRODUCT((A1:A20="Apples")*(B1:B20="Red")*(C1:C 20>15))
If row 1 has column headers, use A2:A20 etc.

20090206, 18:03 #3
 Join Date
 Jun 2005
 Posts
 391
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: counting the # of items in a w/s column (xp & 2003)
I'm sorry. I did not state the objective clearly. I wanted to count each row that had "red" "Apples" as a 1 and there for get an item count of the "Red" "Apple" items. Is this a different formula?
Thanks again.

20090206, 18:26 #4
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting the # of items in a w/s column (xp & 2003)
Hi
If you just want to count for "Red" in ColB and "Apples" in col A and drop your criteria of >15 in col C, drop the last range from Hans' formula
(C1:C20>15)
the formula change to
=SUMPRODUCT((A1:A20="Apples")*(B1:B20="Red"))
** Sumproduct will affect recalculation time if your data is large.
another way is to use Auto Filter and put a formula =SUBTOTAL(3,a2:a20)
HTH
cheers, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090206, 23:21 #5
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts
Re: counting the # of items in a w/s column (xp & 2003)
And if you want to count the red apples then try
=SUMPRODUCT((A1:A20="Apples")*(B1:B20="Red")*C1:C2 0)
StuartR

20090207, 08:15 #6
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting the # of items in a w/s column (xp & 2003)
Stuart
I beleive you means to said " And if you want to SUM the red apples then try ..."
cheers, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090207, 10:16 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: counting the # of items in a w/s column (xp & 2003)
With column C containing quantities of fruit, Stuart's formula calculates the total quantity (count) of red apples, whereas the formulas previously suggested counted the number of rows that have "red" and "apples".

20090207, 13:53 #8
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting the # of items in a w/s column (xp & 2003)
that's correct! Stuart's formula does the summing on col C whereas, the earlier formula did the counting based on the 2 criteria
Hope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090209, 15:57 #9
 Join Date
 Jun 2005
 Posts
 391
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: counting the # of items in a w/s column (xp & 2003)
Thank you