+ Reply to Thread
Results 1 to 14 of 14

What reference can be made for open-ended ranges in using COUNTIFS?

  1. #1
    Registered User
    Join Date
    08-06-2019
    Location
    Cebu
    MS-Off Ver
    2007
    Posts
    36

    What reference can be made for open-ended ranges in using COUNTIFS?

    I have a formula here to count how many times a number appears in the list:
    =COUNTIFS(B1:G--,"number to be counted")

    The thing is, the top cell, "B1" is constant but "G--" is open-ended - that is, the more entries I add at the topmost row the cell reference for "G" will naturally be increased (or pushed down below the list).

    Question: How can make "COUNTIFS" be able to count an open-ended cell reference under the "G" column?

    NOTE: Was supposed to upload this under the "Formula..." category but I get this weird page aboiut "Succuri" firewall, saying my post has "objectionable HTML code". WTF?! I'm just uploading a question with the required Excel worksheet mandated by this forum.
    Either there are some a*holes messing with my account in this forum OR there's this "someone" or "some group" messing with our net connection...

    Update: Tried again to post under "Excel Formulas & Functions" for this concern and there it is again - "Access Denied - Sucuri Website Firewall". Reason posted in the bottom of the page:"Your request was not authorized due to its content (HTML code not allowed)."
    F&*K! Is my post here just now "not allowed"? I think some a*hole is trying to mess up this very, VERY valuable forum.
    Guess the admins need to check into this...
    Attached Files Attached Files
    Last edited by angExcel-entflea; 03-29-2021 at 04:36 AM. Reason: Update as to the weird event in my browser

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    This, maybe:

    =COUNTIFS(B:G,I2)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    You can just use a row that is much larger than the amount of data that you have, e.g. use this in J2:

    =COUNTIFS($B$1:$G$200,I2)

    then copy down.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    08-06-2019
    Location
    Cebu
    MS-Off Ver
    2007
    Posts
    36

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    @Glenn Kennedy @Pete_UK

    Thanks, guys - will use those formulas. Keep you posted on what happened...
    By the way, ever experience weird things happening whenever you try to upload a post in this forum?
    It happened to me just now (see my updated post...)

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    Update: Tried again to post under "Excel Formulas & Functions" for this concern and there it is again - "Access Denied - Sucuri Website Firewall". Reason posted in the bottom of the page:"Your request was not authorized due to its content (HTML code not allowed)."
    F&*K! Is my post here just now "not allowed"? I think some a*hole is trying to mess up this very, VERY valuable forum.
    Guess the admins need to check into this...
    We already know, and it's not a problem. Sometimes what you post can be seen as an HTML code injection, and as such might pose a security risk to the forum and is blocked accordingly. The most common cause is using formulae containing either < or > - the trick is to place a space either side of each so that the forum doesn't block it.

    Please curb your language: this is a public forum, and casting unfounded aspersions, and in such a foul-mouthed way, is not acceptable here. Thank you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    08-06-2019
    Location
    Cebu
    MS-Off Ver
    2007
    Posts
    36

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    @Glenn Kennedy @Pete_UK

    I'm worried that the next time I log into here, I'd find out I got "kicked out" of this forum without any valid reason - or my IP "banned from logging in here indefinitely..."

    Weird as hell...

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    Please read post #5 which explains the issue.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    Calm down. We all swear... some more than most... but this is NOT the place for it.

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

  9. #9
    Registered User
    Join Date
    08-06-2019
    Location
    Cebu
    MS-Off Ver
    2007
    Posts
    36

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    @AliGW

    My sincerest apologies about that - if you've been trying to upload this same post (that should've been in the proper category) for 7 straight times, I guess you'd also go ballistic. But this isn't aimed at everyone in the forum - lately, I've been seeing signs my internet account is getting screwed up by someone. Whoever that is, I hope he/she gets immediate psychiatric help....

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    I suggest you start changing passwords, then. It has nothing to do with this forum, whatever it is, except in the case of anything you type into your posts that can be seen as HTML, as I explained. That will be blocked as a precaution.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    Thanks for the rep. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

  12. #12
    Registered User
    Join Date
    08-06-2019
    Location
    Cebu
    MS-Off Ver
    2007
    Posts
    36

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    @AliGW
    I'm gonna do A LOT MORE than that...
    Whoever is the COVID19-addled psycho-hacker messing up my internet life, he/she is gonna have a hard time doing so in the coming days...

    Thanks again and my apologies again as well - stay safe always...
    Last edited by angExcel-entflea; 03-29-2021 at 07:52 AM.

  13. #13
    Registered User
    Join Date
    08-06-2019
    Location
    Cebu
    MS-Off Ver
    2007
    Posts
    36

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    @Pete_UK @Glenn Kennedy

    One good turn deserves another. Hope my giving you guys reps will help your respective rankings...
    Stay safe always (thumbs up emoji)

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: What reference can be made for open-ended ranges in using COUNTIFS?

    Our membership list is presented to you in order of reps earned, if you are interested in the effect your adding reputation has: https://www.excelforum.com/members/l...putation&pp=30

+ 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. front-ended back ended volume scenarios
    By freshfruit in forum Excel General
    Replies: 1
    Last Post: 01-30-2014, 05:18 PM
  2. [SOLVED] vlookup use with open ended range and array?
    By BoydRed in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2014, 01:28 PM
  3. How do you make an open-ended/dynamic range?
    By kestefon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-13-2013, 01:36 PM
  4. Open-ended list validation
    By m.cain in forum Excel General
    Replies: 1
    Last Post: 02-11-2008, 08:25 PM
  5. Open ended Data Validation List
    By ChemistB in forum Excel General
    Replies: 1
    Last Post: 12-11-2007, 05:40 PM
  6. Average of open-ended column, within given intervals
    By chlor in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-03-2006, 10:58 AM
  7. Open Ended VBA Criteria
    By SamuelT in forum Excel General
    Replies: 8
    Last Post: 10-02-2006, 09:42 AM
  8. [SOLVED] Need open ended cell for Sum range.
    By Cris B. in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-14-2005, 12:05 PM

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