SOLVED

New Contributor

# COUNTIF help

Hi all,
I'm running a COUNT IF in a table, receiving data input from Forms.  The info is and will continue to come in for a long time yet.

The formula I'm using is =COUNTIF(\$D\$2:D2,D2)

As i progress to row 15, it reads =COUNTIF(\$D\$2:D15,D15)

This is so that I have a progressive number generator so that I can individualize every entry.  This serves to feed the table information into a tidier front end.

My problem is that with each new entry, it interprets the count range to be (top of table:bottom of table)

I'd like to be able to have a progressive count function, that takes the row value as being the same as where the formula exists.

In the above, col B has the formula, D is the code being used, E is a combination of, allowing for individual row lookup on the front end.

As entry 110 comes in to row 110, it will change the formula in 109 from =COUNTIF(\$D\$2:D109,D109)

to =COUNTIF(\$D\$2:D110,D109).

That creates headaches where the value of D repeats, as it creates a count of the entire table, I want a count from table top, to current row value only.

Ideally I want this to all happen automatically without having to refresh the formulas.  It also needs to work in Excel online with its reduced functions (no Macros)

I hope all of that makes sense - any help appreciated.

3 Replies
Best Response confirmed by brentonhobden (New Contributor)
Solution

# Re: COUNTIF help

You may replace your formula with the following one to make it work with the self expandable range reference.

=COUNTIF(D\$2:INDIRECT("D"&ROW()),D2)

# Re: COUNTIF help

Thank you VERY much!

Works a treat.

# Re: COUNTIF help

You're welcome @brentonhobden! Glad it worked as desired.