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)
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)