+ Reply to Thread
Results 1 to 5 of 5

Count column blanks IF adjacent column is NOT blank AND another column IS also blank!

  1. #1
    Registered User
    Join Date
    05-10-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Count column blanks IF adjacent column is NOT blank AND another column IS also blank!

    Hi! It's my last day at work and I have been DESPERATELY trying to finish off a project I've been working on here with an excel database, I've been tearing my hair out over this for days, if someone can give me the magic formula in the next 7 hours (wishful much?!) that would be amazing!!

    I have something like this:

    Column A Column B Column C
    (Letter sent) (forms returned) (Review completed)

    I need to know how many forms we are waiting to receive (column B blanks) if a letter has been sent out (column A NON blanks). But there is a spanner in the works!!! Sometimes the review has already been completed (column C) and even though a letter has been sent, we now do not care if the forms aren't returned since the review has been done. So in that instance I need something like; count blanks in column B only if column A HAS data AND if column C is blank

    If someone has a magic formula before I leave work today I'll be so grateful (as will my employers and the many people who will be using this spreadsheet if I can get it to work as it should!).

    I have tried so many COUNTA, COUNTIF (AND (IF etc formulas that my brain is being fried!!! None have worked!! Thanks to anyone who can save me!!!!

    ***NOTE as an aside, while tagging this post I noticed the "countcolour" tag...I'm about to go search on this because it would be an easy solution to my problem as all of the letter sent rows have been coloured yellow and if I can just locate and count all yellow rows it would solve this nightmare for me!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count column blanks IF adjacent column is NOT blank AND another column IS also blank!

    try
    =SUMPRODUCT(--(A1:A20<>""),--(B1:B20=""),--(C1:C20=""))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Count column blanks IF adjacent column is NOT blank AND another column IS also blank!

    Hi,

    Try SUMPRODUCT.

    Please Login or Register  to view this content.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Registered User
    Join Date
    05-10-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Count column blanks IF adjacent column is NOT blank AND another column IS also blank!

    Oh my goodness! BOTH of those work perfectly!!! Thank you both so much! I can't believe how much time I spent scouring forums yesterday and playing with different formulas trying to get something to work and now you have both responded within minutes with a perfect solution!!! thank you so so much!!

    Just wondering, between the two formulas, is there one that may work better than the other? As I will be leaving after today, this spreadsheet needs to work smoothly for the other people in the office as they are not very excel-literate and will not be able to fix things if they go awry...

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Count column blanks IF adjacent column is NOT blank AND another column IS also blank!

    Actually, I didn't see Martin's reply. If I seen wouldn't post it. Both are doing same thing. See here. You can understand various type of sumproduct.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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