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

1. ## 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...

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

This, maybe:

=COUNTIFS(B:G,I2)

3. ## 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. ## 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. ## 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.

6. ## 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...

8. ## 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.

9. ## 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. ## 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. ## 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. ## 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...

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

@Pete_UK @Glenn Kennedy

Stay safe always (thumbs up emoji)

14. ## 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

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