Forum Home
Press F1
 
Thread ID: 102828 2009-09-03 00:49:00 Excel forumla help DeSade (984) Press F1
Post ID Timestamp Content User
806432 2009-09-03 00:49:00 I am a complete n00b with forumla in Excel so forgive me if this is a easy question.

I have a master sheet that takes a simple sum value from 20 or so work sheets and adds them up.

I would like to add another filter to this but not sure how.

=SUM('sheet 1:sheet 20'!F7)

That is how the code is now and that works fine for a overall picture but there is another field on the sheets that I want to work as a filter so

if B7 = "name" then run the sum above

The "name" will have two possible variables

I hope I have explained this well enough. 		DeSade (984)
806433 2009-09-03 01:01:00 Sounds like you need an IF statement:

=IF(B7="NAME",SUM('sheet 1:sheet 20'!F7),0)

This evaluates B7 to see if the word NAME exists in it. If it does, then it sums your range, and if it doesn't it puts 0 in the cell.

Does that make sense? 		nofam (9009)
806434 2009-09-03 01:02:00 Have alook at the SUMIF() function, in the help. the_bogan (9949)
806435 2009-09-03 01:20:00 Sounds like you need an IF statement:

=IF(B7="NAME",SUM('sheet 1:sheet 20'!F7),0)

This evaluates B7 to see if the word NAME exists in it. If it does, then it sums your range, and if it doesn't it puts 0 in the cell.

Does that make sense?

Thanks for that, it looks like it would do the job but its not summing the numbers from F7 on each sheet

I forgot some crucial information
B7 is also across sheet 1 to sheet 20

I tried this but there is something still wrong with it.

=SUMIF('sheet 1:sheet 20'!B7, "name",'sheet 1:sheet 2'!F7) 		DeSade (984)
806436 2009-09-03 02:20:00 If you name your ranges, you can use the sumif function. (Just tried a brief attempt, and it worked okay)

Group the sheets 1 through to 20.

Select the cells in column B, (e.g B1:B500) name them (e.g NAMECHECK)
Do the same with column F, (I named them WHATTOSUM)

Ungroup the sheets.

Go to the sheet you want your formula in, the formula would look like =SUMIF(NAMECHECK,"bob",WHATTOSUM)

I assumed you were looking for bob in column B. 		the_bogan (9949)
806437 2009-09-03 02:37:00 There has to be a easier way of doing that, in your method I would have to individually choose 20 cells from 20 sheets to add them to the WHATTOSUM DeSade (984)
806438 2009-09-03 02:42:00 That's why I suggested grouping the sheets. As long as you have them grouped, you can select the cells on one of the sheets and it will group all the cells in the grouped sheets. the_bogan (9949)
806439 2009-09-03 02:52:00 Oh that sounds alright.
Where is the group command on 2008 please. 		DeSade (984)
806440 2009-09-03 02:58:00 EDIT: Ignore the above... I dunno what I was thinking when I thought it was working. It doesn't.


Try this.

Select the sheet1 tab. Holding Shift, click on the sheet20 tab.

To ungroup, rightclick on one of the tabs and select ungroup sheets. (I'm using 2007, so it may be a bit different) 		the_bogan (9949)
806441 2009-09-03 03:07:00 If you name your ranges, you can use the sumif function. (Just tried a brief attempt, and it worked okay)

Group the sheets 1 through to 20.

Select the cells in column B, (e.g B1:B500) name them (e.g NAMECHECK)
Do the same with column F, (I named them WHATTOSUM)

Ungroup the sheets.

Go to the sheet you want your formula in, the formula would look like =SUMIF(NAMECHECK,"bob",WHATTOSUM)

I assumed you were looking for bob in column B.

Sorry mate you lost me a bit.
Are you saying the above does not work? 		DeSade (984)
1 2