SUMPRODUCT
 

I the following is a nice formula:

=SUMPRODUCT((A6:A20007=A20012)*(B6:B20007=B20012)* (D6:D20007=D20012)*(F6:F20007))

Then I wanted to do a SUMPRODUCT() on items beginning with "42" and someone
in the newsgroup told me:

=SUMPRODUCT((A6:A20010=A20015)*ISNUMBER(MATCH(B200 15&"*",B6:B20010,0))*(D6:D20010=D20015)*(F6:F20010 ))

Where B20015 has "42".

I was thinking I would like to expand this a litte and what if I wanted to
do a group, for instance: "4100 ; 4200 ; 4300 ; 5115 ; 6050"

The data is in A6 to F20007 and what I have done is at B6 insert a column,
and at the now empty B20015 put the string "4100 ; 4200 ; 4300 ; 5115 ; 6050"
and then starting in cell B6 and do COUNTIF(B20015,"*"&C6&"*") and
copy this down to B20007. Then I would use the return values in column B
with 1 to get the SUMPRODUCT() that in effect would include the group
"4100 ; 4200 ; 4300 ; 5115 ; 6050".

This works fine but is there a more direct way to return the value without
doing the COUNTIF method I am doing here.

I want to be able to put the group as a string all in one cell like I have
in B20015.


Thank you for your help,


Steven