+ Reply to Thread
Results 1 to 7 of 7

Counting Blanks based on criteria in another column

  1. #1
    Registered User
    Join Date
    10-23-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Counting Blanks based on criteria in another column

    I am trying to count the number of blank cells in one column based on the criteria of another column. To explain, I have a list of sales advisors in one column, then a column of customers, then a columns of e-mail addresses. I want a formula that will tell me how many blank e-mail addresses there are for each sales advisors. Can you please help anyone?

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,943

    Re: Counting Blanks based on criteria in another column

    =COUNTA(C:C)-COUNTA(B:B)

    Where are e-mail in C column, and B are sales advisors. This is for tatal blanks.

    For eaxh sales advisor, in next column write:

    =SUMPRODUCT(--($B$2:$B$17=B2),--(C2:C17<>""))

    (extend range)
    Never use Merged Cells in Excel

  3. #3
    Forum Contributor
    Join Date
    04-23-2009
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2003 work, 2007 home
    Posts
    199

    Re: Counting Blanks based on criteria in another column

    =countblank(A:A) assuming data is in column A

  4. #4
    Registered User
    Join Date
    10-23-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Counting Blanks based on criteria in another column

    Thanks for the responses but this is not what I am looking for. I don't think I explained myself very well I will give an example

    Ashley Mr K Ladva [email protected]
    Ashley Mrs S Majid [email protected]
    Chris Mrs J Collins [email protected]
    Chris Mr T Razoki [email protected]
    Ashley Mrs S Lyons [email protected]
    Ashley Miss S Goodall [email protected]
    Chris Mrs N Standing [email protected]
    Chris Mr M Jones [email protected]
    Chris Mr A Nikolov
    Chris Mr P Halpin [email protected]
    Ashley Miss R Tariq
    Chris Ms J Nogollos
    Chris Miss E Egbuna
    Chris Mr T Branch [email protected]
    Chris Mrs R Patel [email protected]
    Chris Mr M Oakley [email protected]

    From this table I want a formula that will tell me how many blank e-mails each exec has. So Chris has not retrieved an e-mail for 3 customers and Ashley only for 1 customer. So the idea it I can write a formula and just change the exec in the formula. I hope that makes better sense.

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,943

    Re: Counting Blanks based on criteria in another column

    Something like this:

    Book1.xls

  6. #6
    Registered User
    Join Date
    10-23-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Counting Blanks based on criteria in another column

    Thats pretty complicated but it seems to work, thanks a lot.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,943

    Re: Counting Blanks based on criteria in another column

    B, C and D columns (name of e-mail owner) don't have to be separated. I just didn't want to join them together. So you only write name of person you want to test or write all names and use same formula.

    Or you can use Pivot table:

    Book1(1).xls

    So I don't think that one formula is complicated

    (Please, make thread [solved] when you'll be satisfied with solution)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1