What formula to use

I have a worksheet with 2 tabs - tab 1 has names
Tab 2 has a $total in each line, in the next row across you can pick from a dropdown box a name that is on the first tab, this follows across the line etc... at the end of the row we count how many cells contain data and this is divided into the $total a the first line to give a price per name.
Each line is the same.

What formula should I use to look up the name and sum everything....
I have used sumif, it will only work if there is one name?

Any suggestions/help most appreciated

sounds like you want to use sumproduct

Basically when using sumproduct you can total values based on a certain criteria. e.g. names are in row 1 and values in row 2 you would enter in row 3:

=sumproduct(($A$1:$Z$1=A1)*($A$2:$Z$2))

If you copy the above across row 3 you will get a total for all values in row 2 for each Name in Row 1.

Obviously this is a simplified version for what you need but it is not neccessarily the easiest function to understand without an example.

Also when using the sumproduct function you need to set and lock the array area, you cannot set a whole column or row as the array as for some reason it doesn't like A:A or 1:1

Regards
Mark

Hi Mark thanks for the

Hi Mark
thanks for the advice, I will have a look and let you know how I get on..... can I upload an example?
cheers
K

Hi Krunchi, That would be

Hi Krunchi,

That would be helpful

Regards
Mark