Sumproduct with defined group

[jjjjj55555]

I'd like to use the Insert - Name - define function to define a group. The group in this case consists of 4 different labels. They are a mixture of text & numbers. For example i've entered these in cells A1-A4

BB-75
76
BB-77
BB-78

Then I want to use this group (Named "BB") and use it in a sumproduct formula so if my data is in columns C & D, and the my data that could match the BB group is in column C, with numerical entries in column D, I get the total of all entries in column D with corresponding entries in column C equal to one of the items in my defined group.

EG Of expected result:

C.............D
BB-75.......10
90............6
BB-78.......15

I would like my sumproduct formula to return 25

Is this possible?

Thanks in advance for the help.

[Paul]

I think this might work for you, but it's not SUMPRODUCT. I used an array formula SUM(IF(..

=SUM(IF(NOT(ISERROR(LOOKUP(C1:C4,A1:A4))),D1:D4))

After typing this in, you must press CTRL+SHIFT+ENTER, not just ENTER.

This looks up the values in C1:C4 in your list A1:A4, and if found will return the sum of those values in D1:D4 corresponding to only those entries that match.

Adjust your ranges to suit.