"Sumif " across worksheets?

[Graham H]

Not sure if this is a programming or function group query so apologies if wrongly posted.
I have a variable range of worksheets. In each worksheet in the cells C41:C46 there are
names, all of these cells may or may not contain a name. If any are missing it will always
be the last cells in the range of each worksheet. In cells E41:E46 there are scores which
relate to the corresponding name in the same row. Some of the names may be repeated in the
other worksheets, and there will be new names in some worksheets. What I want to do is
have a separate worksheet which lists all the names in the worksheet range and totals all
the scores against each name. I would value any guidance on this.

Graham

 

[SteveDB1]

Hi Graham,
sumif will do this.
I use it all the time between worksheets.

=sumif(ShtNm!CriteriaRng, Criteria, SumRng) becomes for your case
=sumif(ShtNm!c41:c46,"CritieraName" OR ShtNm!c41, ShtNm!e41:e46)

Where ShtNm is the name of your worksheet. You'll need the exclamation mark
after the sheet name. CriteriaRng is the range of your criteria you're
looking through. If you want to limit the formula to a specific name, you can
place double quotes around the text of name you want to look for. If you have
enough variations in names, you can just place a cell reference for that
sheet's name.
The SumRng is the range of values you want summed up. If you need it to
reference a different sheet, just place the worksheet's name in from of that
range with an exclamation.

This function will however only allow one criteria. There is another
worksheet function called Sumproduct that will allow up to 30 criteria in
older excel, and 255 criteria in Excel 2007.
That'd become

=sumproduct((ShtNm!CriteriaRng1=Criteria1)*(ShtNm!CriteriaRng2=Criteria2)*(ShtNm!CriteriaRng3=Criteria3)....*(ShtNm!SumRng))

Same rules apply it just allows for more criteria ranges and testing.
You can have your criteria in a few different options.
1- as a cell reference, on the same sheet or a different sheet. If it's
located on a different sheet than the equation's location, place the ShtNm!
in from of the range.
2- As a name reference-- "Name" will "lock" it to that name.

Hope this is helpful. I use Sumif, and Sumproduct all the time and have
found them both most useful tools.

 

[Graham H]

Steve,
Thanks for your comprehensive reply, I am very grateful. I have used Sumif so many
times but had not considered it as a solution to this particular problem. After a few
amendments I have it working fine now in my situation. Many thanks again.

Graham