SUMIF over multiple spreadsheets
 

Hi,

I would like to use the following formula over 30+ worksheets in the same workbook but I do not want to have to repeat sum if over and over again.

I have tried my own research and most people suggest sumif with sumproduct and indirect but I cannot get my head round it.

This is the formula I am using at the moment:

=(SUMIF('SMITH S'!V$4:V$22,C5,'SMITH S'!W$4:W$22))

'Smith S' is an employee name and the other sheets are all named in that format. I have created a list and defined a name for it called 'tabs'. I understand you need to do this for the indirect function?

Can some body reply with the formula I need to use that includes my other 30 worksheets please?

Hi,

Assuming "Smith S" is recorded in cell A7 (amend this to suit), the formula would be:

=SUMIF(INDIRECT(A7&"!V$4:V$22"),C5,INDIRECT(A7&"!W $4:W$22"))

HOWEVER, you cannot use spaces in the tab names. So you would have to have "Smith_S" or "SmithS" or "Smith.S" or something similar.

As for needing to define a range name for INDIRECT to work.... Never had to do that. You just need the tab names in a list that you will refer to.

Hey

Thanks for replying - I tried your advice but I am getting the REF error - can you take a look at the attached please and see where I am going wrong?

I would like the formula on the 'SUMMARY' tab to the right of the 9 digit codes...where I started my original SUMIF formula.

Hi Mark,

I'm not 100% sure I understand what you're trying to do on your summary sheet. Could you explain a little more?

Spencer.

The 490240-012 is a list of profit and loss codes - these codes will appear in the individual tabs and I would like to add up the net expense total of 490240-012 and all the other codes each time it appears for each employees tab.

Take a look at the attached, it's what I have just changed around - some of the errors still remain though and not sure why.

Could any of the codes appear more than once on an individual employee tab?

Possibly - but not really. If the code was already on the spreadsheet then the net expense would go against that code. no reason to add the same code...could this be the problem?

I see what you mean now....and yes they would and do appear more than once on an individual tab.....

Right, then the approach I've just tried might not be what you're after.

I shall mock up another way of doing it.
I've PM'd you with an email address as the file will be too big to post back here by the time the formulas are added as the file size limit on this forum is tiny!

SUMIF over multiple spreadsheets
 

Hi Mark

IMO your layout is wrong.

I would be more inclined to have 1 sheet to record all the entries in
columns, the mega benefit of this is that you dont have to worry about
complex formulas branching across several sheets.

Date | Emp | Expense | Amount | VAT


You could then look at a matrix grid for your Summary Sheet.

You could also have another sheet which you could use for one-off
employee search | View | Print on a Monthly/Annual basis....

This would mean you will eliminate the need for multiple tabs for each
employee, which would expand & contract due to attrition.

I would be happy to redo your existing and send it to you for your perusal.

Cheers
HTH
Mick.