Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Countif only if another column is not blank...

This is a discussion on Countif only if another column is not blank... within the Excel Questions forums, part of the Question Forums category; Hello, I have a list of parts in column A that is about 200 rows or so. There are only ...

  1. #1
    New Member
    Join Date
    Dec 2008
    Posts
    6

    Default Countif only if another column is not blank...

    Hello,

    I have a list of parts in column A that is about 200 rows or so. There are only a dozen parts or so that repeat multiple times. I then have about 20 columns of data for each part. Column K is "Customer".

    What I need to do is count the number of each part in column A. That was done with a simple countif formula. Now I need to count each part in column A only if a cumster name is listed column K (this column is left blank until a customer is added). I can't seem to get this to work. Let me know if you need me to be more specific.

    Please help!

    Thanks!

  2. #2
    Board Regular
    Join Date
    Jun 2008
    Location
    Cleveland, OH
    Posts
    343

    Default Re: Countif only if another column is not blank...

    if the data was in a1:a200 and the customer name was in column k, something like (in example, enter in l1) =if(k1<>"",countif($a$1:$a$200,a1),"") would count how many times the value in a happens in the entire range only if there were a customer name...is that what you were looking for?

  3. #3
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: Countif only if another column is not blank...

    If you're doing the count by product in some sort of summary table (ie 20 rows -- 1 per product) I would first suggest you try using a Pivot Table.

    Failing that you can use a Sumproduct.

    If we assume in A210 you have the first product to summise:

    B210:=SUMPRODUCT(--($A$1:$A$200=$A210),--($K$1:$K$200<>""))

    The above would count the number of times product as specified in A210 appears in range A1:A200 where Customer is not blank.
    Does my a$$ look big in this picture ?

  4. #4
    Board Regular
    Join Date
    Jun 2008
    Location
    Cleveland, OH
    Posts
    343

    Default Re: Countif only if another column is not blank...

    DonkeyOte...Could you explain the sumproduct formula you used above. I often see that used and it is obviously more efficient however i truly don't understand how it works. Is there a brief explanation of that method? Thanks.

  5. #5
    New Member
    Join Date
    Dec 2008
    Posts
    6

    Default Re: Countif only if another column is not blank...

    That didn't seem to work. It only returned the numer of times the part appears in colunm A. Here is an example:

    A K
    Part Customer
    Widget A XYZ Corp.
    Widget B
    Widget A
    Widget B Joe Smith
    Widget A ABC Corp
    Widget C

    There are three "Widget A" with two customers listed. I need excel to only count the two parts with the customer listed.

    So in this exmaple the result shold be:

    Widget A 2
    Widget B 1
    Widget C 0

    Hopes this helps.

    Thanks!

  6. #6
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: Countif only if another column is not blank...

    EB08,

    Bob's page here is a good read on the subject:

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    In this specific case here is an example with smaller sample:

    Sheet4

     AK
    1ProductCustomer
    21A
    32B
    43 
    54D
    61 
    72A
    83 
    94A
    101 
    111C
    121D
    131 
    142A
    153A
    164 
    174F
    185A
    191F
    202 
    21  
    22Product 
    2314

    Spreadsheet Formulas
    CellFormula
    K23=SUMPRODUCT(--($A$1:$A$20=$A23),--($K$1:$K$20<>""))


    Excel tables to the web >> Excel Jeanie HTML 4

    Sumproduct is calculated as follows:

    SPA_121208_201440

     ABCD
    1Formula Location:Sheet4'!$K$23  
    2Formula: =SUMPRODUCT(--($A$1:$A$20=$A23),--($K$1:$K$20<>""))  
    3Result: 4  
    4Delimiter(s):,*;  
    5Component Part(s):"--($A$1:$A$20=$A23)""--($K$1:$K$20<>"")" 
    6Array Row(s):2020 
    7Array Column(s):11 
    8    
    9Calculation:Result(s)Result(s)Total(s)
    101010
    112111
    123010
    134000
    145010
    156100
    167010
    178000
    189010
    1910100
    2011111
    2112111
    2213100
    2314010
    2415010
    2516000
    2617010
    2718010
    2819111
    2920000


    Excel tables to the web >> Excel Jeanie HTML 4
    Does my a$$ look big in this picture ?

  7. #7
    Board Regular
    Join Date
    Jun 2008
    Location
    Cleveland, OH
    Posts
    343

    Default Re: Countif only if another column is not blank...

    I did in l1 =if(k1<>"",a1,"") and copied it down then created a list of all products...for example in m1 i would have the product apple. in n1 i put =countif($l$1:$l$200,n1)...i believe it got the answer you would be looking for but there is probably a more efficient way out there.

  8. #8
    Board Regular
    Join Date
    Jun 2008
    Location
    Cleveland, OH
    Posts
    343

    Default Re: Countif only if another column is not blank...

    Thanks for the link...looks quite helpful.

  9. #9
    New Member
    Join Date
    Dec 2008
    Posts
    6

    Default Re: Countif only if another column is not blank...

    Thanks DoneyOte! That works if I use specific cell references (A1:A200), but I would like to use just column A (A:A) since this spreadsheet is constantly growing and shrinking as data is added and deleted.

    If I use the formula:

    =SUMPRODUCT(--(A:A="Cidermill"),--(K:K<>""))

    I get a #NUM! error. What am I doing wrong?

    Thanks!

  10. #10
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: Countif only if another column is not blank...

    Pre-XL07 you can't use entire column references with Sumproduct, that said you should not set your ranges to be too large anyway as it will impact performance... give yourself some breathing room for sure but the larger the ranges you use the greater the impact the formulae will have on your file as a whole in terms of performance. You could look into using dynamic named ranges but without knowing the particulars of your setup it's hard to comment (in terms of blanks interspersed in ranges etc...) -- if you do a search on dynamic named ranges you should find plenty of examples if you opt to go that route.
    Does my a$$ look big in this picture ?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com