+ Reply to Thread
Results 1 to 10 of 10

Empty cells and countif

  1. #1
    Registered User
    Join Date
    11-21-2011
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Empty cells and countif

    Unknown amount of participants (1-50) will give values 1-5 in two polls (kysely), from poll A we need only values 1-4.
    In Sheet2 people make same poll later.
    in Sheet3 it subtracts Sheet2 results from Sheet1.
    Then it should pick up how many gave >=1 in poll b than poll a, this works fine. In my example 2.
    Then it should pick up how many gave <1 in poll b than poll a, but excludes if somebody gave 5 in poll a. In my example 28, should be 6.
    It wonīt exclude fives from sheet1. I tried many different formulas, but couldnīt get it working.
    Is there easy way to get rid of those extra zeros? i used paste link in 2 first sheets and was able to hide zeros from Excel options, but i need empty cells B2:B51 in Sheet3.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: Empty cells and countif

    A2: =IF(Taul1!A2="","",Taul1!A2)
    B2: =IF(AND(Taul2!B2="",Taul1!B2=""),"",Taul2!B2-Taul1!B2)

    Not sure if this gives the result you are looking for:

    =SUMPRODUCT(--(B2:B51<>""),--(B2:B51<>0),--(B2:B51<1))-IF(Taul1!B2:B51<=4,)

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Empty cells and countif

    Hi pjotr

    Welcome to the forum

    you can add an If function to check for blank cells

    =IF(Taul1!A6<>"",Taul1!A6,"")
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  4. #4
    Registered User
    Join Date
    11-21-2011
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Empty cells and countif

    Quote Originally Posted by TMShucks View Post

    Not sure if this gives the result you are looking for:

    =SUMPRODUCT(--(B2:B51<>""),--(B2:B51<>0),--(B2:B51<1))-IF(Taul1!B2:B51<=4,)
    This works, but still includes fives from sheet1. -IF(Taul1!B2:B51<=4,) doesn't do anything.
    For example, I have 1,1,1,5,5,5 in first poll and 2,2,2,3,3,3 in second. Formula should give 0, but it count's fives and gives 3 (3-5= <1).

    Thanks for the help so far.

  5. #5
    Registered User
    Join Date
    11-21-2011
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Empty cells and countif

    Still need help!!
    How can I add (Sheet1!B4:B53<=4;) to this =IF(AND(Sheet2!B13="";Sheet1!B13="");"";Sheet2!B13-Sheet1!B13)
    Have tried everything, but it just doesn't work.
    Last edited by pjotr; 12-09-2011 at 02:26 AM.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Empty cells and countif

    Hi pjotr,

    It can be simply added like:-

    =IF(AND(Sheet2!B13="",Sheet1!B13="",Sheet1!B4:B53<=4),"",Sheet2!B13-Sheet1!B13)

    I would suggest you to attach your file which will help forum to help you better.

    Regards,
    DILIPandey
    < click on below 'Star' if this helps >
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  7. #7
    Registered User
    Join Date
    11-21-2011
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Empty cells and countif

    I have already tried that, but didn't work. Sheet1!D4:D53<=4 is also in Sheet3!D5 but it doesn't do anything.
    D7 and D20 should be empty in Sheet3, because of 5s in Sheet1. Only important result I need, is in Sheet3!D3:M3.
    In current situation, D3 should be 75.
    Attached Files Attached Files

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Empty cells and countif

    Hi pjotr,

    I am getting an error while opening this file that "converter failed to open the file".
    Try re-attaching the file.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  9. #9
    Registered User
    Join Date
    11-21-2011
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Empty cells and countif

    I think it's because of language settings.
    I translated it to english, i hope you can open it now.
    Formulas are not working for me anymore, but i hope you can see it correctly.
    Attached Files Attached Files

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Empty cells and countif

    Hi pjotr,

    I see all #NAME in the excel and could'nt understand it further.
    I believe there is some language issue with my Excel.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ 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