Countif with index match where multiple columns match

[Peteypirate]

I have a data matrix which has order information running vertically, and cost options running across the top. If the order has the option then it shows the optionvalue in the corresponding cell, otherwise it is blank.

On a separate tab using the following formula I am able to do a count foreach option, by region, how many times it is spec’d;

=COUNTIFS(INDEX('Cost Option Values'!$6:$1048576,0,MATCH(“OPTIONCODE”,'Cost OptionValues'!$4:$4,0)),"<>"&"",'Cost OptionValues'!$E$6:$E$1048576,”REGION NAME”)
This works perfectly.
However, I now want to change the formula to count groupsof options (all options codes that begin with the same 5 letters).

I know I need to change part of my formula to “OptionCode Prefix”&”*”, but I don’t know how to change the index and match partto count from multiple matching columns.

Any help would be appreciated.

 

[steve the fish]

You will not be able to use ranges that big with this or the spreadsheet will struggle to cope but use this and changes ranges to suit data:

=SUMPRODUCT((LEFT($A$4:$Z$4,17)="OPTIONCODE PREFIX")*($A$6:$Z$100<>"")*($E$6:$E$100="REGION NAME"))

 

[Peteypirate]

Hi, this works but as you said does not work with a range this big. Is there a maximum size of range this would work with?

 

[steve the fish]

It will work with what you have but will be very slow. Just put in appropriate ranges not to the end of the sheet. I presume you dont have 1000000 rows of data.