Another Excel help thread

[Passacaglia]

I'm trying to use the sumif function, but instead of a column of cells in my test range, I want to make my test range the same cell in 20 different tabs. So here's my formula:

=SUMIF('40033:61101'!$A$2,$A$2,'40033:61101'!$B$26)

Where 40033....61101 are the 20 tabs. If cell A2 of that tab is the same as cell A2 of the tab my formula is in, I want to add cell B26 of that tab. But sumif doesn't like it when I go across tabs like that instead of a range within one tab.

Any ideas?

[Bobble]

I think you have to go with SUMPRODUCT. I stole this from teh intarwebs:


Two options...

1]

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))

where SheetList refers to a range that lists the relevant sheet names, A2:B10 the relevant range in every sheet in SheetList, and D2 the criterion value.

2] Using the morefunc.xll add-in...

=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))

where Sheet2 and Sheet3 are the relevant sheets while A2:B10 is the relevant range and D2 the criterion. <!-- / message --><!-- sig -->

[SteveMax58]

I was thinking something like what Bobble had posted in Option 1. But I dont think you're looking for the product.

Do you have Excel 2007 or 2003?

[Passacaglia]

Right, I'm not looking for a product -- just a sum. I'm not really following Bobble's example right now, but I've got a decent enough workaround going now anyway. I have Excel 2003.

[SteveMax58]

Without using an aggregation Worksheet (or range of cells) you could use 20 SUMIF statements.

If that sounds like a terrible idea (or 2003 does not support that many characters/functions in a formula)...I think you might need to just make an aggregation worksheet (or range of cells) to each perform a SUMIF for you. Then you could just add that range of cells.

[Mike Lowe]

I have a different Excel question and instead of starting a new thread I figured I'd threadjack here for a minute:

I have an excel sheet which I'd like to have a cell where I'd input a letter and then have that letter generate a pre-written response into another cell.

I can do this with simple letters being counted across a row or whatever, but I want literally one letter to represent a pre-written statement:

John Smith - A (Spelling errors) or something along those lines.

[SteveMax58]

I have a different Excel question and instead of starting a new thread I figured I'd threadjack here for a minute:

I have an excel sheet which I'd like to have a cell where I'd input a letter and then have that letter generate a pre-written response into another cell.

I can do this with simple letters being counted across a row or whatever, but I want literally one letter to represent a pre-written statement:

John Smith - A (Spelling errors) or something along those lines.

So you want to have (for example) cell A1 as "John Smith", cell B1 as [enter a letter], and cell C1 as [pre-written text] ?

If so...use VLOOKUP. Like this (in cell C1):

=VLOOKUP(B1, [2x2 Range of cells], 2, FALSE)

EDIT to add: Make sure your letters are furthest to the left in your 2x2 range of cells.

[Bobble]

Right, I'm not looking for a product -- just a sum. I'm not really following Bobble's example right now, but I've got a decent enough workaround going now anyway. I have Excel 2003.

Yeah, SUMPRODUCT can be very confusing. It's just a way to get an array of values to multiply times an array of whether to USE those values.
So you need an array of all the values in cell A2 of all the sheets and an array of 1's and 0's signalling which of those values to use. SUM the PRODUCT of those two arrays and you effectively have a SUMIF across sheets.

Anyway, I attached a sample worksheet using method 1. I put values in cell A6 of 3 sheets. My criteria for whether to sum them is in D2 ("> 0"). The formula is in A1. Oh, and the list of sheet names is in cells G6:G8 and is a named range called "SheetList". Hope it helps.

[Passacaglia]

Yeah, SUMPRODUCT can be very confusing. It's just a way to get an array of values to multiply times an array of whether to USE those values.
So you need an array of all the values in cell A2 of all the sheets and an array of 1's and 0's signalling which of those values to use. SUM the PRODUCT of those two arrays and you effectively have a SUMIF across sheets.

Anyway, I attached a sample worksheet using method 1. I put values in cell A6 of 3 sheets. My criteria for whether to sum them is in D2 ("> 0"). The formula is in A1. Oh, and the list of sheet names is in cells G6:G8 and is a named range called "SheetList". Hope it helps.

I think I get it. The thing is, I need to take the info from the 40 tabs I already have and put it in 11 new tabs. Would I then need 11 different ranges, one for each tab?

The good news is, though, that I managed to get everything I needed by going to the tabs that my initial 40 tabs were pulling from, so the problem is solved!

[Mike Lowe]

So you want to have (for example) cell A1 as "John Smith", cell B1 as [enter a letter], and cell C1 as [pre-written text] ?

If so...use VLOOKUP. Like this (in cell C1):

=VLOOKUP(B1, [2x2 Range of cells], 2, FALSE)

EDIT to add: Make sure your letters are furthest to the left in your 2x2 range of cells.

Still lost, sorry!

Joe Smith 1 Student struggling with spelling concepts

How would I make it so that cell 1C, when a "1" is entered in cell 1B, says "Student struggling with spelling concepts"

[Bobble]

I think I get it. The thing is, I need to take the info from the 40 tabs I already have and put it in 11 new tabs. Would I then need 11 different ranges, one for each tab?

The good news is, though, that I managed to get everything I needed by going to the tabs that my initial 40 tabs were pulling from, so the problem is solved!

Is it different "SUMIF" criteria for the 11 tabs? Then you're just looking at a seperate SUMPRODUCT statement for each tab.

If I want to sum every value greater than 7 from the 40 tabs and put that on Tab1, then Tab1 would just have the SUMPRODUCT formula in cell A1 with a criteria of "> 7" in cell D2. If you wanted to sum every value less than 3 and put that on Tab2, then Tab2 would have the SUMPRODUCT formula in it's A1 and a criteria of "< 3" in it's D2. Or, am I not understanding?

[Passacaglia]

Mike --

Take one of your tabs, and call it 'key' In that tab, in Column A, put the numbers. In Column B, put the corresponding messages. So Cell A1 will say "1" and Cell B1 will say "Student struggling with spelling concepts"

In another tab, use column A to enter your numbers, and in Cell B1, paste this formula:

=VLOOKUP(A1,key!$A:$B,2,0)

Then copy Cell B1, and paste it down for all the new numbers you've typed into Column A.

[Bobble]

Still lost, sorry!

Joe Smith 1 Student struggling with spelling concepts

How would I make it so that cell 1C, when a "1" is entered in cell 1B, says "Student struggling with spelling concepts"


You have a table in two columns, say, cells D1:E3 that looks like this:
D | E
1 | Student struggling
2 | Student stinks
3 | Student slow on the uptake

Then in cell C1 you put that vlookup formula =vlookup(B1, D1:E3, 2). Its telling excel to look up the value in B1, find that value on the table in D1 : E3, and print out the value in the second column of that table. In this case it looks at B1 and finds a 1. It goes to the table, finds 1 and then looks across to the second column and would return back with "Student struggling".

[Passacaglia]

Is it different "SUMIF" criteria for the 11 tabs? Then you're just looking at a seperate SUMPRODUCT statement for each tab.

If I want to sum every value greater than 7 from the 40 tabs and put that on Tab1, then Tab1 would just have the SUMPRODUCT formula in cell A1 with a criteria of "> 7" in cell D2. If you wanted to sum every value less than 3 and put that on Tab2, then Tab2 would have the SUMPRODUCT formula in it's A1 and a criteria of "< 3" in it's D2. Or, am I not understanding?

No, this was exactly right. Thanks!

[Mike Lowe]

Thanks guys, got it!

[Passacaglia]

No, this was exactly right. Thanks!

Oh, with one caveat -- I wasn't able to copy and paste that cell all over the tab, so I used the address function, and ended up with a formula like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!$A$2"),$A$2,INDIRECT(ADDRESS(ROW(B14),COLUMN(B14),,,""&SheetList&""))))

[Bobble]

Oh, with one caveat -- I wasn't able to copy and paste that cell all over the tab, so I used the address function, and ended up with a formula like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!$A$2"),$A$2,INDIRECT(ADDRESS(ROW(B14),COLUMN(B14),,,""&SheetList&""))))

Hmm, I've never used ADDRESS myself. You learn something new every day...

[Passacaglia]

Glad I could return the favor!

[SteveMax58]

Thanks guys, got it!

Good deal.

Yeah, I probably confused things by saying a "2x2" table. Really I just meant AT LEAST 2 columns and as many rows as you have need for.