=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$3&"'!A1:A10 0"),"=John",INDIRECT("'"&$H$1:$H$3&"'!B1:B100") ))

Change "=John" to whatever you want to compare (could be of form "=" & X1
where X1 is value to be compared)

Cells H1 to H3 contain names of your worksheets: extend as needed

And obviously change ranges A & B as required.


And ask the right question if you want a "right" answer!

"Yiannos" wrote:

The function I want to use is the "sumif" and i don't think it works this way.

Is there another way?

"Toppers" wrote:

Create tow shetts named "Start" and "End" and place shhets to be summed
between these then:

=SUM('Start:End'!A5) will sum A5 in all sheets between Start & End

HTH

"Yiannos" wrote:

Excel says that the formula I entered is too long...
I have many spreadsheets with various data and I need the formulae to fet
information from all my spreadsheets but it is too long...

I have made a list of all my spreadsheet tab names...
is it possible to have a short formula, with excel using this list of tab
names?

e.g. I have 3 spreadsheets 'A','B','C'. I need the sum of all A5 cells in
each of the spreadsheets. The conventional formula will be:
Sum(A!A5;b!A5;c!A5).

If there are many spreadsheets with longer names and a morecomplicated
function imagine how frustrating it can get!

Is it possible to use one formula with variable spreadsheet instead of
including the spreadsheet names in hte formula?

Thank you