+ Reply to Thread
Results 1 to 10 of 10

countif, criteria match and blank cell

  1. #1
    Registered User
    Join Date
    01-16-2021
    Location
    england
    MS-Off Ver
    office prifessional 16
    Posts
    6

    countif, criteria match and blank cell

    Hi Have this formula in spread sheet and need a little help.


    =COUNTIFS('Overdue Actions'!B5:B1048576,"Craig", 'Overdue Actions'!O5:O1048576,"Overdue",'Overdue Actions'!P5:P1048576,">=183",'Overdue Actions'!P5:P1048576,"<365")

    I need this to show the 0 value as blank cell, I have tried suggestion on the IF at the start etc and worked, but it also blanked cells that met the criteria. ie. blanked cells for 0 results, but also blanked cells that had 12 in etc.

    Any help, appreciated.
    Thanks
    Mark

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: countif, criteria match and blank cell

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Registered User
    Join Date
    01-16-2021
    Location
    england
    MS-Off Ver
    office prifessional 16
    Posts
    6

    Re: countif, criteria match and blank cell

    Struggling to get this trimmed to belwo the maximum file load - ive only got 6 lines cant trim as all needed data.

    will keep trying

  4. #4
    Registered User
    Join Date
    01-16-2021
    Location
    england
    MS-Off Ver
    office prifessional 16
    Posts
    6

    Re: countif, criteria match and blank cell

    I have put a few lines on excel in a zip files.

    Thanks
    Mark
    Attached Files Attached Files

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: countif, criteria match and blank cell

    hi tripski007
    The requirements are not strict,
    like greater than 6 below 8 ????
    like greater than 10 below 12 ????
    like greater than 25 ?????

    Low <=4
    Medium >=5 and <=6
    High >=8 and <=10
    Very High >=12 and <=25

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: countif, criteria match and blank cell

    I may not be understanding, but does this work?

    =IF(COUNTIFS('Overdue Actions'!$B$5:$B$1048576,A2, 'Overdue Actions'!$O$5:$O$1048576,"Overdue",'Overdue Actions'!$P$5:$P$1048576,">=183",'Overdue Actions'!$P$5:$P$1048576,"<365")=0,"",COUNTIFS('Overdue Actions'!$B$5:$B$1048576,A2, 'Overdue Actions'!$O$5:$O$1048576,"Overdue",'Overdue Actions'!$P$5:$P$1048576,">=183",'Overdue Actions'!$P$5:$P$1048576,"<365"))

  7. #7
    Registered User
    Join Date
    01-16-2021
    Location
    england
    MS-Off Ver
    office prifessional 16
    Posts
    6

    Re: countif, criteria match and blank cell

    Thanks Wk9128- the requirements were not my issue, the attached was a stripped version on the actual sheet the info I was after related to making any zeros found display blank.
    all the same thank you - which I now have.

  8. #8
    Registered User
    Join Date
    01-16-2021
    Location
    england
    MS-Off Ver
    office prifessional 16
    Posts
    6

    Re: countif, criteria match and blank cell

    Thanks Greg11 This worked, I had originally tried the =IF and failed, but after seeing your post I noticed Id missed the "comma" after the =0.

    Thanks Buddy

  9. #9
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: countif, criteria match and blank cell

    Please select the H2:L2 area, and then enter the formula, the array formula , drag down

    HTML Code: 
    Please select the C2:F2 area, and then enter the formula, the array formula , drag down
    HTML Code: 
    Last edited by wk9128; 01-18-2021 at 04:05 AM.

  10. #10
    Registered User
    Join Date
    01-16-2021
    Location
    england
    MS-Off Ver
    office prifessional 16
    Posts
    6

    Re: countif, criteria match and blank cell

    Thank you Wx9182

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIFS, If no criteria match, leave cell blank, else return value
    By eemiller1997 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-03-2017, 02:10 AM
  2. Replies: 5
    Last Post: 07-02-2017, 10:25 AM
  3. [SOLVED] How to return value blank cell, if criteria do not match
    By devawad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-25-2013, 08:14 AM
  4. [SOLVED] How to return value blank cell, if criteria do not match
    By devawad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-25-2013, 05:21 AM
  5. [SOLVED] How to get excel to ignore criteria if cell is blank? (Min/Max/Average/Countif)
    By nat.ssnt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2013, 06:22 AM
  6. I want to exclude blank cell in index match formula with multiple criteria
    By NOOR8225 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-16-2013, 06:51 AM
  7. [SOLVED] COUNTIF, dates and blank cell criteria
    By luvthavodka in forum Excel General
    Replies: 6
    Last Post: 06-01-2006, 04:55 PM

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