+ Reply to Thread
Results 1 to 9 of 9

Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    Hi everyone,

    Been sitting for over an hour and tearing my hair out now, so just had to ask.

    I have a column which calculates a percentage value. On this column I use an IFERROR and "" to return a blank cell where I get errors. Typically, this is the bottom row or two.

    My problem is that I want to count the numbers in this column and Excel counts the cells containing text also. I also want the count to be conditional and count only if variable Order = HL in some other column. I've tried various COUNTIFS and just can't seem to get it correct:

    COUNTIFS(OFFSET(G10,0,0,E6),"HL",OFFSET(H10,0,0,E6),"<>"& "")


    You may want to ignore the OFFSET part, but observe the "<>"&"" for my attempt on counting everything which is not text.

    What am I missing?

    Thanks in advance!

    Elijah

    Uten navn.png
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    Hi,

    =SUM(COUNTIFS(G10:G21,"HL",H10:H21,{"<=",">"}&0))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    Hello there, XOR LX,

    I could not seem to get this formula work. Is it supposed to be an array formula considering the {}? I tried that also.

    Thanks.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    If your happy to change the Iferror to return - you could use
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    EDIT
    The formula supplied by XOR LX works fine for me

  5. #5
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    Hi, Fluff,

    Thanks, but I'd prefer to keep blank cells! Blame it on my OCD.

    It's odd that the formula does not work with me. I use Excel Translator to translate the formula directly, but get an error upon trying to enter it. If anyone of you could be troubled to make the change in the actual sheet I uploaded and re-upload, Excel would translate automatically when I download.

    But as it is, I can't seem to be able to implement it myself...

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    2 other options
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    Quote Originally Posted by Elijah View Post
    Hello there, XOR LX,

    I could not seem to get this formula work. Is it supposed to be an array formula considering the {}? I tried that also.

    Thanks.
    No, not an array formula. For a Norwegian version of Excel you just require a different separator for the array constant, viz:

    =SUM(COUNTIFS(G10:G21;"HL";H10:H21;{"<="\">"}&0))

    (in addition to translating the actual functions, of course.)

    Regards

  8. #8
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    Thanks a lot to the both of you!

    Indeed, it was the separator which was the issue. That did the trick, XOR LX.

    Many thanks guys.

    Have a good night or day.

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Trouble with COUNTIF formula on conditional count - I want to avoid counting text.

    You're welcome & thanks for the feedback

+ 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. [SOLVED] Avoid Double Counting - Ignore Text in Criteria
    By SamFitz in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-29-2016, 09:19 AM
  2. Trouble with COUNTIF, to count a cell or not
    By HSCM in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-10-2014, 11:00 AM
  3. [SOLVED] Countif formula to count cells that contain everything but a certain text
    By nbrp in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-03-2014, 03:26 PM
  4. [SOLVED] Trouble with nested IF-COUNTIF Formula
    By Ford Sakata in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-10-2013, 01:45 PM
  5. [SOLVED] Using COUNTIF to count text derived by formula
    By DWG3 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-20-2013, 10:12 PM
  6. CountIf Color - Conditional counting
    By diggy13 in forum Excel General
    Replies: 5
    Last Post: 07-11-2010, 09:52 PM
  7. Avoid counting rows/columns with formula but no values
    By magnuc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2006, 06:30 AM

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