Sum items' total in all sheets

[YasserKhalil]

Hello everyone

I have Data sheet in which I need to calculate the total in column F for each item in all sheets (Which names begin with "Per St *")

For example: Item named "ItemTest" should be summed in sheets("Per St 1" & "Per St 2" & "Per St 3" & "Per St 4")
So it would be : 3 + 2 + 1 + 3 = 9
Note that the items in the four sheets are listed in two lists (Column B & F)
Another point these four sheets are just examples : there would be more sheets ..
Thanks advanced for help

[FDibbins]

Im having a hard time seeing what you want, mainly because of a translation problem

Whete do these come from?

So it would be : 3 + 2 + 1 + 3 = 9

[YasserKhalil]

From sheets("Per St 1" & "Per St 2" & "Per St 3" & "Per St 4")
range("C8") in these four sheets but note that not all the items are in one list .. the items splitted in two lists in these sheets

[YasserKhalil]

I hope it is clear
the general idea is how to sum from several sheets for specific item ..(the sheets numbers are not static they are variable

[YasserKhalil]

Waiting for any help
please I need help in this topic
Thanks advanced

[YasserKhalil]

If there is no solution, it will be good to give me any idea that can help me

[oeldere]

Is you reply on #2, forummembers can work with that information.

It seems the question is not clear to the forummembers.

So please add the result manualy in your file, and mark the related cells with the same color.

[YasserKhalil]

@ oeldere

Thanks for reply
I attached the file again with three examples coloured to illustarte my request
Note that : the sheets started with "Per St *" are variable

[oeldere]

J4 =SUM('Per St 1:Per St 4'!$C$8)

See the green cells in the attached file.

[YasserKhalil]

Thanks a lot
What if I added a sheet named "Per St 5" ??

I mentioned that the sheets' names which started with "Per St *" are variable .. There will be more sheets so I need dynamic solution.

Another point I need to depend on the item name (eg. ItemTest2) in the SUM process as the item may be different in its address in the sheets
Hope it is clear now

[oeldere]

Please Login or Register  to view this content.

Refer also to ST 5.

J4 =SUM('Per St 1:Per St 5'!$C$8)

Please Login or Register  to view this content.

That is not how you showed it in the file.

[YasserKhalil]

Mr. oeldere
I know that I didn't show "Per St 5" in the file ...
I need flexible solution as new sheets will be added every day and it will be exhausted to edit the formula after every change ...
Another point ... In the attachment the cell address are similar but in later sheets some items will be vanished so the address of cells won't be the same.
I mean in your formula you depend on C8 in a formula , and this will be incorrect as the related item in the future would be in different cell , so I need to depend on the item name like using "SUMIF"
Generally thanks for offering help

I will attach another file.. Look at yellow cells in all sheets to get my request

[oeldere]

@YasserKhalil

Why not add a representive file, in the first place.

If that is the case the offered solution won't work.

1) Why not all data in 1 sheet (instead of several sheets),

2) and all data in 1 range (instead of serveral ranges).

It makes it much easier to analyze the data.

[YasserKhalil]

As for post 12 >> Is everything clear now?

[oeldere]

Maybe you are able to answer the questions in #13.

[YasserKhalil]

@oeldere
This is the structure of the workbook
I have one main sheet which is named "Store" and other sheets is considered the sales for every day (similar to report) and it is required to put the data in these other sheets in two lists (so as be easy to be printed) ..
So I will have many sheets or reports and I have to keep them all in this way.
Hope it is clear now
I know it is complicated issue but I have an idea of using temporary sheet that gather information from all these sheets and treat them easily as this temp sheet will be one sheet and one range (as desired) and at last to have the results copied and pasted ..

[cyiangou]

Add this function:

Please Login or Register  to view this content.

Now just enter '=GetItemTestCount(B4)' in cell F4 and copy down.

[YasserKhalil]

@cyiangou
Thanks you very very much
I thought for a moment that my request is impossible ..the UDF function works like charm
Thanks a lot for second gift for today (But the first gift is still needs some fixes)

Thanks a lot for this great help