Range selection

[Ridimz]

I have the following on multiple worksheets:
TAMCN NOMEN Poss DL
A0001 Name1 2 0
A0002 Name2 3 1
A0003 Name3 5 1
A0004 Name4 4 3
A0005 Name5 3 0
B0001 Name6 5 0
B0002 Name7 36 5
B0003 Name8 16 1
B0004 Name9 2 0
B0005 Name10 36 4
C0001 Name11 3 0
C0002 Name12 8 1
C0003 Name13 2 0
C0004 Name14 2 0
C0005 Name15 1 0
D0001 Name16 0 0
D0002 Name17 17 1
D0003 Name18 11 3
D0004 Name19 71 13
D0005 Name20 5 0

I would like to get a total "Poss" for all "A####", "B####", "C####", and
"D####". How would I go about this? This should allow for adding or removing
worksheets later which would be automatically included in or removed from
the total.

Thank you in advance.

 

[Bernard Liengme]

Let us assume the data is on Sheets 1 to Sheet10 with the TAMCN in column A,
Names in B, Pos values in C. For now we will assume 100 items in a column
(change as needed)
There is a problem with conditional formulas working across sheets. SO we
must do calculations on each sheet. Do it one and copy to others, or group
the sheets and do it all together.
In some suitable blank range on each sheet (say AA1:AA4) enter
"A","B","C","D",
in AB1 enter =SUMPRODUCT((LEFT($A$1:$A$100,1)=AA1)*($C$1:$C$100))
Copy this down to AB4

We will add these value together. Now insert a sheet for the summary, rename
it Summary.
You want to be able to add sheets. Before Sheet1, insert a sheet and name it
First. After Sheet10, insert a sheet and call it Last. Always get these
blank but ad new sheets between them (you will need to copy AA1:BB4 from a
old sheet to each new sheet added).

On the Summary sheet make a column with value A,B,C,D and beside the A enter
=SUM(First:Last!AB1). Copy this down to sum other cells.

Best wishes
Bernard
www.stfx,ca/people/bliengme

 

[BS]

Hi Ridimz;

I've added another column next to A
into cell B2 =LEFT(A2;1)
so i now have sth like this:

A0001 A Name1 2 0


Now we can get the total !

=SUMIF($B$2:$D$21;"A";$D$2:$D$21)

and for letter B:

=SUMIF($B$2:$D$21;"B";$D$2:$D$21)

maybe a solution.

Burçin SARIHAN