Forum Discussion
Countif/Countifs
It appears my response may have not gone through, apologies if this is a repeat. My goal is to be able to take the range from the box I wrote it (e.g. C6:C341) and using it in place of the range in the countif function I have above. The reason is because as you can see in the next few columns the range changes and so "dragging" the function I put in the box wouldn't have the accurate ranges I want. The criteria doesn't change and I do not need a way to change that.
=SUM(COUNTIF(INDEX(C:C,@CHOOSECOLS(REGEXEXTRACT(TEXTSPLIT(C3,":"),"[0-9]+"),1)): INDEX(C:C,@CHOOSECOLS(REGEXEXTRACT(TEXTSPLIT(C3,":"),"[0-9]+"),2)),{6.12.23.60}))The above formula returns the expected result in Excel 365, Excel for the web and Excel 2024. We have to make sure that there isn't a circular reference. The formula is in cell C5 and dragged to the right in row 5. That's why we can only count the occurrences of {6.12.23.60} starting from row 6 in order to avoid a circular reference. This means that all ranges between C6:C1048576 are allowed. In your formula i see one occurrance of COUNTIF*COUNTIF but i assume you always want COUNTIF+COUNTIF.
This does not appear to be working. I get this on my screen. I apologize if I'm doing something incorrect. I do always want countif+countif. Unfortunately, sometimes my ranges will be in the center of a count and may not have empty space above it. Hopefully that won't change anything.
