Simply a formula

[bernie1973]

Afternoon

Can this formula be simplified:

=COUNTIF('2017'!$AK$8:$AQ$51,"a")*12.25+COUNTIF('2017'!$AK$8:$AQ$51,"a11")*11+COUNTIF('2017'!$AK$8:$AQ$51,"a10")*10+COUNTIF('2017'!$AK$8:$AQ$51,"a9")*9+COUNTIF('2017'!$AK$8:$AQ$51,"a8")*8+COUNTIF('2017'!$AK$8:$AQ$51,"a7")*7+COUNTIF('2017'!$AK$8:$AQ$51,"a6.25")*6.25+COUNTIF('2017'!$AK$8:$AQ$51,"a6")*6+COUNTIF('2017'!$AK$8:$AQ$51,"a5")*5+COUNTIF('2017'!$AK$8:$AQ$51,"a4")*4+COUNTIF('2017'!$AK$8:$AQ$51,"a3")*3+COUNTIF('2017'!$AK$8:$AQ$51,"a2")*2+COUNTIF('2017'!$AK$8:$AQ$51,"a1")*1

I require from worksheet 2017 for the range AK8:AQ51 to add ("a")*12.25+("a11")*11+("a10")*10+("a9")*9+("a8")*8+("a7")*7+("a6.25")*6.25+("a6")*6+("a5")*5+("a4")*4+("a3")*3+("a2")*2+("a1")*1<o></o>

Thanks in advance

 

[barry houdini]

Hello bernie1973,

You can shorten by using a single COUNTIF and using "array constants" for the range of criteria and multipliers, i.e. like this:

=SUM(COUNTIF('2017'!$AK$8:$AQ$51,{"a","a11","a10","a9","a8","a7","a6.25","a6","a5","a4","a3","a2","a1"})*{12.25,11,10,9,8,7,6.25,6,5,4,3,2,1})

 

[bernie1973]

Brilliant,

You have saved me hours of work and a long headache, really appreciated

Hello bernie1973,

You can shorten by using a single COUNTIF and using "array constants" for the range of criteria and multipliers, i.e. like this:

=SUM(COUNTIF('2017'!$AK$8:$AQ$51,{"a","a11","a10","a9","a8","a7","a6.25","a6","a5","a4","a3","a2","a1"})*{12.25,11,10,9,8,7,6.25,6,5,4,3,2,1})