Reduce formula

[trimiii]

HI,

I am using the following formula which is too long.. any idea how to short this?


=COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!J6:J105,">="&A4,'A-MAP'!J6:J105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!L6:L105,">="&A4,'A-MAP'!L6:L105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!N6:N105,">="&A4,'A-MAP'!N6:N105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!P6:P105,">="&A4,'A-MAP'!P6:P105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!R6:R105,">="&A4,'A-MAP'!R6:R105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!T6:T105,">="&A4,'A-MAP'!T6:T105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!V6:V105,">="&A4,'A-MAP'!V6:V105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!X6:X105,">="&A4,'A-MAP'!X6:X105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!Z6:Z105,">="&A4,'A-MAP'!Z6:Z105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!AB6:AB105,">="&A4,'A-MAP'!AB6:AB105,"<="&B4)+COUNTIFS('A-MAP'!B6:B105,Sheet1!$A$1,'A-MAP'!AD6:AD105,">="&A4,'A-MAP'!AD6:AD105,"<="&B4)

 

[jasonb75]

It doesn't look too long to me.

If it works, don't try to fix what isn't broken. Shorter alternatives will take longer to calculate.

 

[RoryA]

Perhaps something like:

=SUMPRODUCT(('A-MAP'!B6:B105=Sheet1!$A$1)*('A-MAP'!J6:AD105>=A4)*(MOD(COLUMN('A-MAP'!J6:AD105),2)=0)*('A-MAP'!J6:AD105<=B4))

 

[trimiii]

Thanks RoryA