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.
Sorry for the late reply. It seems that either i didn't receive a notification of your reply on Oct 06, 2025 or i was missing something. Please use the formula provided by djclements​ that is perfect for your task. I wasn't aware that this is possible. Attached is my sample file with both formulas however mine is redundant.
Not sure why there wasn't any follow-up here, but you probably just need to change the system separator used in the static array for it to work with your regional and language settings (change the periods to commas or semi-colons), e.g. use {6,33,38} instead of {6.33.38}. INDIRECT will also work, if you're not opposed to its volatility:
=SUM(COUNTIF(INDIRECT(C345),{6,33,38,45,46,51,52,56,60}))I hope that helps. Cheers!
