Forum Discussion
Countif/Countifs
=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!
