SUMIF MULTIPLE SHEETS

[MikeMN]

I have used the sumif formula many times ofer the years.

However, I have always only refrences one sheet.

I am now tying to refrence several sheets.

But I am getting a #value error message.

Any thoughts if this can be done.

Thank you

Mike

 

[Deified]

Sorry to bring up old threads but this is almost exactly what i was looking for. Except i have one variation. Instead of referencing a list of cells that refrence my spreadsheet names. Can i use a wild card for the refrence? for example...

All of my tabs start with "WE" and then end with the date of the week. So i am trying to refrence each tab using this formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&WE?&"'!A:A"),C8,INDIRECT("'"&WE?&"'!D:D")))

So between the &'s i want to reference any spreadsheet with the text string "WE". How would i do that?

 

[Aladin Akyurek]

Sorry to bring up old threads but this is almost exactly what i was looking for. Except i have one variation. Instead of referencing a list of cells that refrence my spreadsheet names. Can i use a wild card for the refrence? for example...

All of my tabs start with "WE" and then end with the date of the week. So i am trying to refrence each tab using this formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&WE?&"'!A:A"),C8,INDIRECT("'"&WE?&"'!D:D")))

So between the &'s i want to reference any spreadsheet with the text string "WE". How would i do that?

I don't think that's possible formulawise. We need (a) list of possible sheets or (b) a procedure that generates them.

Example for (b)...

"Sheet"&ROW(INDIRECT("1:"&X2))

where X2 = 7.

 

[Deified]

I don't think that's possible formulawise. We need (a) list of possible sheets or (b) a procedure that generates them.

Example for (b)...

"Sheet"&ROW(INDIRECT("1:"&X2))

where X2 = 7.

hmm. I kind of get what you are saying but dont understand your example. If i were to generate a list of sheet names starting from A2 and going to A300 using the code that is on page 1 of this thread. Would i be able to auto update the list as i make new tabs just by running the code again?

 

[Aladin Akyurek]

hmm. I kind of get what you are saying but dont understand your example. If i were to generate a list of sheet names starting from A2 and going to A300 using the code that is on page 1 of this thread. Would i be able to auto update the list as i make new tabs just by running the code again?

Yes, it would.

 

[Deified]

excellent... Now i cant seem to get the code to work. I created a module (i think) and when i enter the forumla as specified and pres CTRL+ Shift + Enter i get #NAME? as the result.

 

[Deified]

ahh google is a great friend. I figured it out with a macro.

I had to modify it so that i got the resutls in the cells i wanted. Perhaps it can be cleaned up a bit, but it works.

Sub ListSheetz()
 Dim i%
 For i = 1 To Sheets.Count
 Cells(i, 1).Value = Sheets(i).Name
 Next i
  Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-12
    Range("B9").Select
    ActiveSheet.Paste
   Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("B8").Select
 End Sub

Thanks for you help though!

 

[Aladin Akyurek]

excellent... Now i cant seem to get the code to work. I created a module (i think) and when i enter the forumla as specified and pres CTRL+ Shift + Enter i get #NAME? as the result.

ahh google is a great friend. I figured it out with a macro.

I had to modify it so that i got the resutls in the cells i wanted. Perhaps it can be cleaned up a bit, but it works.

[...]

Thanks for you help though!

Great. Thanks for providing feedback.

 

[funkbuqet]

I have tried option 1, but I keep getting a #value errors out of the indirect formula. Below is the exact formula that I used. With Sheetnames being a named range for the cells that contain the sheet names. When I use evaluate formula and step through it, the range names come together correctly, but when it goes to close out the first indirect function the array of ranges all change to #value errors. I know that the indirect arguments are written correctly because if I remove sumproduct from the formula it works fine, albeit only for the first entry in the list.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetnames&"'!B5:B100"),A2,INDIRECT("'"&Sheetnames&"'!E5:E100")))

As near as i can tell, this formula should work, but indirect seems to be having issues with the array. What am I doing wrong?

Edit: Nevermind. I figured it out. I had a single #REF error caused by a non matching sheet name hiding within and causing all of the #Value errors.

 

[Helpdesk]

Hi, Aladin or other Excel expert,
I'm rather new to Excel. How do you set up the range '&SheetList&', mentioned in method 1? BTW, why are 2 levels of quotes required inside the INDIRECT parens?

I have posted in another thread [3527876], but hope for help here as well...

Thanks for this one, I was using SUMIF's all over the place and the workbook was acting like it had a case of the flu!

In my case, I'm playing around with this to troll income and expense categories, to see how far I can go with a simple profit and loss system. I'm using ranges to get Excel to allow nested Validated Lists for tagging expense and income items and it's all fine and dandy, even the tax exclusions and component reporting.

I'm being lazy, and assuming all transactions are a tax input or a tax output ..unless tagged otherwise, so that I only have to record the exceptions, hence the divide by 11 to get the 10% tax component out.

The sheets' data areas are like this:
[TABLE="width: 1402"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]
[/TD]
[TD]Credit Crd: Business
[/TD]
[TD]
[/TD]
[TD]Dr
[/TD]
[TD]Cr
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Expenses
[/TD]
[TD][/TD]
[TD]Income
[/TD]
[/TR]
[TR]
[TD]17/07/2013
[/TD]
[TD]-15.95
[/TD]
[TD="colspan: 2"]O'REILLY MEDIA 0104 SEBASTOPOL 13.99 USD 0.46 AUD
[/TD]
[TD]-$15.95
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CAF
[/TD]
[TD]Helpdesk
[/TD]
[TD]QHSE
[/TD]
[TD]PD, Subscriptions, Resources
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


But....

I have two cost centres running off of the same transactions, and I'm wondering how to slip in a condition to the formula to give me the CAF paid on purchase (an accounting category of GST, items over $1,000):

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",INDIRECT("'"&SheetList&"'!f2:f2000")))
/11

THe formula works fine as it is, but covers both cost centres,

...I'd like to interrogate column I for a particular cost centre "Helpdesk", and only return CAF when "Helpdesk" is in column I on those CAF hits.

sooooooo .... any geniuses out there?

And yes, I can get around it by either:
- having separate tax columns for each cost centre; or
- prefixing the tax code with a cost-centre code

..both are messy in their own way, and I'm trying to get this to be user-friendly; both usage and visually.

 

[Aladin Akyurek]

I have posted in another thread [3527876], but hope for help here as well...

Thanks for this one, I was using SUMIF's all over the place and the workbook was acting like it had a case of the flu!

In my case, I'm playing around with this to troll income and expense categories, to see how far I can go with a simple profit and loss system. I'm using ranges to get Excel to allow nested Validated Lists for tagging expense and income items and it's all fine and dandy, even the tax exclusions and component reporting.

I'm being lazy, and assuming all transactions are a tax input or a tax output ..unless tagged otherwise, so that I only have to record the exceptions, hence the divide by 11 to get the 10% tax component out.

The sheets' data areas are like this:
[TABLE="width: 1402"]
<tbody>[TR]
[TD]Date[/TD]
[TD][/TD]
[TD]Credit Crd: Business[/TD]
[TD][/TD]
[TD]Dr[/TD]
[TD]Cr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Expenses[/TD]
[TD][/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]17/07/2013[/TD]
[TD]-15.95[/TD]
[TD="colspan: 2"]O'REILLY MEDIA 0104 SEBASTOPOL 13.99 USD 0.46 AUD[/TD]
[TD]-$15.95[/TD]
[TD][/TD]
[TD][/TD]
[TD]CAF[/TD]
[TD]Helpdesk[/TD]
[TD]QHSE[/TD]
[TD]PD, Subscriptions, Resources[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


But....

I have two cost centres running off of the same transactions, and I'm wondering how to slip in a condition to the formula to give me the CAF paid on purchase (an accounting category of GST, items over $1,000):

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",INDIRECT("'"&SheetList&"'!f2:f2000")))
/11

THe formula works fine as it is, but covers both cost centres,

...I'd like to interrogate column I for a particular cost centre "Helpdesk", and only return CAF when "Helpdesk" is in column I on those CAF hits.

sooooooo .... any geniuses out there?

And yes, I can get around it by either:
- having separate tax columns for each cost centre; or
- prefixing the tax code with a cost-centre code

..both are messy in their own way, and I'm trying to get this to be user-friendly; both usage and visually.

Are you intending to have...

=SUMPRODUCT(
  SUMIFS(
   INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",
   INDIRECT("'"&SheetList&"'!i2:i2000"),"Helpdesk",
   INDIRECT("'"&SheetList&"'!f2:f2000")))

which is possible on a post-203 system?