Count then Multiply

Ok I think this one is complicated, I've not figured out how to do it. Here's a sample data field I'm just gonna use simple clothing items for this.


A2-----B2------C2 --- ..... Z2
Hat----Shirt --Shoe---- How Much (A number added up from the price list below)


How many dollars the pieces are worth

F6 Hat----F7 20
G6 Shirt---F8 30
H6 Shoe---F9 45

The objective for me is to get a total cost if a person wanted to buy certain items. The actual sheet is huge so If statements won't work.

Any ideas on how to get this to work?

Try:

=SUMPRODUCT(($F$6:$H$6=$A$2:$C$2)*A3:C3*$F$7:$H$7)

adjust ranges to suit

As long as items in A2:C2 are listed in same order as items in F6:H6

Originally Posted by Grasor

I can't seem to get that one to return a value. Also there seems to be an error in the forumla you gave me or I just don't understand what's going on.

=SUMPRODUCT(($F$6:$H$6=$A$2:$C$2)*A3:C3*$F$7:$H$7)

What cells A3:C3 for?

Also do the arrays all need to be in order to work? Ie. first cell in both is "hat" etc.

A2:C2 would contain headers.. and A3:C3 would contain quantities of each to buy.

Yes, the items must be in the same order in both lists.