Guys

I have a spreadsheet with approx 15 sheets in. In the sheets are approx 150 lines of data each sheet.

I want to locate certain data that is on a line with a certain text string and sum their total.

I.e on a line I have 150 in one cell and the word UDL in the other. Is there a way I can on one sheet run a macro or something to find all the cells that have 150 in that are UDL’s?

For example accross 5 sheets I have 10No. 150’s therefore my sum cell will show 1500.

Cheers

I reckon a combination of sumif and start:end sheet summing might work but i can’t work on it right now as on train.

Its a boring train journey so Ive cracked the netbook out just for you 😉

Ive tried anywhichway and cant get complex sum functions to operate across sheet ranges. But you can still use the simple sum function across sheet ranges. So What Id recommend is pasting a SUMIF function in a consistent location across the 15 sheets (lets say AA1 for now)

=SUMIF(A1:A10, “UDL”, B1:B10)

This sums the values in B next to incidences of UDL in column A for example.

Then in an end sheet you can use the following formula to sum all of the results in the AA1 cells in all 15 sheets:

=SUM(sheet1:sheet15!AA1)

where sheet 1 and sheet 15 are the two end sheets in your workbook range of 15 sheets.

Not ideal, but robust and simple to audit.

I tried getting the SUM of sheet range functions to use SUMIF etc but it doesnt like it, even as an array forumla. Someone with more experience may know a work around.

Cheers man 🙂 Legend 🙂