Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    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.

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

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

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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".

  8. #8
    3 Star Lounger
    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

  9. #9
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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
  •