Sum Same Cell in Multiple Worksheets based on Condition which is Single.

[lundbhaiz]

I have this summary tab which is same format like other tabs in the distinct group like trains1, trains2, trains3, till trains50 in my file. and I adding values of all same cells in the summary tab in the same cell. for expmple, I am summing A110 of all worksheets into the cell A110 of the sumamry tab but what I want is it should do based on condition. I want to add only specific cells of sheets which satisfy my condition of a cell value in all worksheets.

I tried finding in search option but did not get good results which i can refer . Can you pleaze help ?

 

[villy]

What is your condition?
I think it can only be done through VBA.

 

[lundbhaiz]

my condition is the cell value for example A4 in all sheets should not contain some text like trains1. so what i want is i want to sum all sheets where the cell A4 in all sheets does not have "trains1" as its value. can you help ,e ?

 

[villy]

If A4 is your condition then which cells of each sheets do you want to add i am assuming this is a numeric values...If you could provide a sample it will be better not only for me but for those who are willing to help you also..\Thanks

 

[lundbhaiz]

a4 is my condition in all sheets. i want add A110 of all sheets which are numeric values based on condition of cell a4 which i describe above. it is very simple. what is complicated ?i dont know what kind sample you are expecting.

 

[Manohar M G]

hi...

I read comments...i have few question regarding trains..

1. Where text "Trains_1" to "Trains_50" will be displayed..
2. You are puting values in cell or only text in cell
3. You are written number of worksheets. So how many worksheets and what is there in each worksheet..

 

[villy]

Try this supposed sheet1 is your summary sheet.
so the trains sheets starts in sheet2

Sub AddCell()
For i = 2 To Sheets.Count
    If Sheets(i).Range("A4") <> "trains1" Then 'Change text to suit
        Sheets(1).Range("A110").Value = Sheets(1).Range("A110") + Sheets(i).Range("A110")
    End If
Next
End Sub

 

[lundbhaiz]

1. Where text "Trains_1" to "Trains_50" will be displayed..
Ans. this will be displayed in summary tab in two different cells for exmple cell B1 and B2.

2. You are puting values in cell or only text in cell
Ans. i put numeric values in cells A110 across all worksheets. same cell but different values which i want to sum based on condition. the condition is put in summary tab. if I put "trains1" in one cell B1 and put "trains40" in cell B2 on summary tab.,it should sum values of cells A110 in all worksheets from trains1 to trains40 excluding sheets trains41 to trains50.
3. You are written number of worksheets. So how many worksheets and what is there in each worksheet..
Ans. there are 80 worksheets. above is just example. actual data is different which is priovate. cannot share on public. but importantly., all worksheets from trains1 to trains50 format and cell ranges is exactly same.

thanks you for help. please help and please ask if more question. thanks you.

 

[lundbhaiz]

Try this supposed sheet1 is your summary sheet.
so the trains sheets starts in sheet2

Sub AddCell()
For i = 2 To Sheets.Count
    If Sheets(i).Range("A4") <> "trains1" Then 'Change text to suit
        Sheets(1).Range("A110").Value = Sheets(1).Range("A110") + Sheets(i).Range("A110")
    End If
Next
End Sub

thanks you but there are many worksheets like 80 worksheets in total and which i want to avoid some of them so the macros where line sheets(1) will return incorrect result. also., to give you more information, the sheets trains1 to trains50 are in contionuos order. there are manyt more worksheets in file which i want to avoid. this program will return incorrect results. thanks you very much for helping sir.

 

[MrVillareal]

You can use this formula:

=SUMPRODUCT((SUBTOTAL(9,INDIRECT("'"&A1:A50&"'!A4"))>0)*(SUBTOTAL(9,INDIRECT("'"&A1:A50&"'!A110"))))

Number of worksheet 50
Cell with condition A4 for each sheet
Cell to add A110 for each sheet

In the sum sheet i plot all the names of 50 worksheet from A1 to A50 and with the same sheet I use the formula above.

Hope this will help.