Countifs - using multiple cell references as part of one criteria

Hi there, I am using a countifs formula to count all values meeting a variety of conditions - here is an example of what I have used below. You will see that it repeats from $D$24 to $D29 to $D34 and so on. Ideally I would love to create a formula that would allow me to reference these three cells along with cells every 5 rows after that (in the same D column which ends at $179) without having to repeat the whole formula (I almost tried but I keep hitting my character maximum!) I've been scratching my head trying to figure out a way to do this but it seems that Excel won't let you create multiple cell references within an array, only constant values. Is there a way around this?

=COUNTIFS('Client_Demographic 2'!$V$3:$V$50000,T$1,'Client_Demographic 2'!$AC$3:$AC$50000,$G$2,'Client_Demographic 2'!$H$3:$H$50000,"?*",'Client_Demographic 2'!$I$3:$I$50000,"Active",'Client_Demographic 2'!$L$3:$L$50000,0,'Client_Demographic 2'!$M$3:$M$50000,0,'Client_Demographic 2'!$N$3:$N$50000,0,'Client_Demographic 2'!$AA$3:$AA$50000,$D24+COUNTIFS('Client_Demographic 2'!$V$3:$V$50000,T$1,'Client_Demographic 2'!$AC$3:$AC$50000,$G$2,'Client_Demographic 2'!$H$3:$H$50000,"?*",'Client_Demographic 2'!$I$3:$I$50000,"Active",'Client_Demographic 2'!$L$3:$L$50000,0,'Client_Demographic 2'!$M$3:$M$50000,0,'Client_Demographic 2'!$N$3:$N$50000,0,'Client_Demographic 2'!$AA$3:$AA$50000,$D29)+COUNTIFS('Client_Demographic 2'!$V$3:$V$50000,T$1,'Client_Demographic 2'!$AC$3:$AC$50000,$G$2,'Client_Demographic 2'!$H$3:$H$50000,"?*",'Client_Demographic 2'!$I$3:$I$50000,"Active",'Client_Demographic 2'!$L$3:$L$50000,0,'Client_Demographic 2'!$M$3:$M$50000,0,'Client_Demographic 2'!$N$3:$N$50000,0,'Client_Demographic 2'!$AA$3:$AA$50000,$D34) +COUNTIFS('Client_Demographic 2'!$V$3:$V$50000,T$1,'Client_Demographic 2'!$AC$3:$AC$50000,$G$2,'Client_Demographic 2'!$H$3:$H$50000,"?*",'Client_Demographic 2'!$I$3:$I$50000,"Active",'Client_Demographic 2'!$L$3:$L$50000,0,'Client_Demographic 2'!$M$3:$M$50000,0,'Client_Demographic 2'!$N$3:$N$50000,0,'Client_Demographic 2'!$AA$3:$AA$50000,$D39) +COUNTIFS('Client_Demographic 2'!$V$3:$V$50000,T$1,'Client_Demographic 2'!$AC$3:$AC$50000,$G$2,'Client_Demographic 2'!$H$3:$H$50000,"?*",'Client_Demographic 2'!$I$3:$I$50000,"Active",'Client_Demographic 2'!$L$3:$L$50000,0,'Client_Demographic 2'!$M$3:$M$50000,0,'Client_Demographic 2'!$N$3:$N$50000,0,'Client_Demographic 2'!$AA$3:$AA$50000,$D44)

Thanks,

David

Can you post a sample file with expected results.

I apologise it's a bit difficult because it is a work document with internal client data. With that countifs formula, I am trying to count number of clients residing in a particular county and meeting a number of demographic criteria. The "Client_Demographic 2" reference is to sift through data from another tab with internal client data. The repeating cell reference ($D24,$D29,$D34,$D39,$D44 etc) is pulling clients (with certain demographic information) from different counties. The reason I am pulling county information as well as region information is because there are data entry issues where the county and region both have blank/incorrect values so I'm trying to present the data both ways. Is there a way to consolidate the formula to something along the lines of $D29 to ($D24,$D29,$D34,$D39,$D44) and eliminate all of the summing that I make (the + signs between all of the countif formulas).
Cheers,
David

I don't know if this will work (I am no guru) hence my request for sample data:

Enter with Ctrl+Shift+Enter

=COUNTIFS('Client_Demographic 2'!$V$3:$V$50000,T$1,'Client_Demographic 2'!$AC$3:$AC$50000,$G$2,'Client_Demographic 2'!$H$3:$H$50000,"?*",'Client_Demographic 2'!$I$3:$I$50000,"Active",'Client_Demographic 2'!$L$3:$L$50000,0,'Client_Demographic 2'!$M$3:$M$50000,0,'Client_Demographic 2'!$N$3:$N$50000,0,'Client_Demographic 2'!$AA$3:$AA$50000,CHOOSE({1,2,3,4,5},$D$24,$D$29,$D$34,$D$39,$D$44))

What is in the cells 'between' the desired $D24,$D29,$D34,$D39,$D44 cells?
So D25:D28, D30:D33, D35:D38 ??

If those 'between' cells will never be a match to cells in 'Client_Demographic 2'!$AA$3:$AA$50000 (or at least not on the same rows where all the other criteria are true as well).

Then you can do this

=SUMPRODUCT(COUNTIFS('Client_Demographic 2'!$V$3:$V$50000,T$1,'Client_Demographic 2'!$AC$3:$AC$50000,$G$2,'Client_Demographic 2'!$H$3:$H$50000,"?*",'Client_Demographic 2'!$I$3:$I$50000,"Active",'Client_Demographic 2'!$L$3:$L$50000,0,'Client_Demographic 2'!$M$3:$M$50000,0,'Client_Demographic 2'!$N$3:$N$50000,0,'Client_Demographic 2'!$AA$3:$AA$50000,$D24:$D44))

@jonmo1

would this work ??

=SUMPRODUCT(COUNTIFS('Client_Demographic 2'!$V$3:$V$50000,T$1,'Client_Demographic 2'!$AC$3:$AC$50000,$G$2,'Client_Demographic 2'!$H$3:$H$50000,"?*",'Client_Demographic 2'!$I$3:$I$50000,"Active",'Client_Demographic 2'!$L$3:$L$50000,0,'Client_Demographic 2'!$M$3:$M$50000,0,'Client_Demographic 2'!$N$3:$N$50000,0,'Client_Demographic 2'!$AA$3:$AA$50000,(IF(MOD(ROW(24:44),5)=4,D24:D44))))

Originally Posted by JohnTopley

would this work ??

I doubt it, but try it and see. You tell us if it works..

Johnmo you win the prize for the correct formula - thank you very much! And yes you are right that the between cells (D25:D28, D30:D33, D35:D38 and so on) didn't contain any values. I didn't realise you could nest a countifs formula within a sumproduct. That's really handy. Thanks again for your help! John Topley, I tried your formula as well but sorry I couldn't get it to work.

You're welcome

David,
Thank you for the feedback. As you will have gathered from my response I am receiving (learning) as much (if not more) as I am giving in replying to the threads.

I often look at responses simply to acquaint myself with new (to me) solutions: the trouble is at my age, I forget them quickly.

Anyway, glad you have a solution.