Forum Discussion
Countif/Countifs
I'm trying to do a standard countifs formula for several columns of information. Each column is using the same criteria but has different ranges. I'm trying to make an equation that will be easy to drag so I do not have to rewrite the range each time. Does anyone know how to do this? I have cells with the prewritten ranges I'll want to use. I can give more information if that helps people understand.
7 Replies
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.
Yes, more information would be very helpful. Ideally, you'd make available a copy of the actual workbook. At the very least an image. As the old saying goes, "A picture is worth a thousand words."
So here is my dilemma. The "6" "33" "38" etc will remain the same across the row from 013(1st) to 079 (1st) BUT the range changes each time. I've written the range I want my countif function to reference so if there's a way to insert it I can just drag the equation across keeping the criteria the same while changing the range's to fit my needs.
