# Thread: counting the # of items in a w/s column (xp & 2003)

1. ## 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

2. ## 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.

3. ## 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.

4. ## 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, francis

5. ## 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

6. ## 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, francis

7. ## 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".

8. ## 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

9. ## Re: counting the # of items in a w/s column (xp & 2003)

Thank you

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•