PDA

View Full Version : Excel forumla help

DeSade
03-09-2009, 12:49 PM
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.

nofam
03-09-2009, 01:01 PM
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?

the_bogan
03-09-2009, 01:02 PM
Have alook at the SUMIF() function, in the help.

DeSade
03-09-2009, 01:20 PM
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)

the_bogan
03-09-2009, 02:20 PM
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.

DeSade
03-09-2009, 02:37 PM
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

the_bogan
03-09-2009, 02:42 PM
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.

DeSade
03-09-2009, 02:52 PM
Oh that sounds alright.
Where is the group command on 2008 please.

the_bogan
03-09-2009, 02:58 PM
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)

DeSade
03-09-2009, 03:07 PM
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?

toonttm
04-09-2009, 04:36 AM
problem with programs like Excel is they want you to drag-and-drop what you want to do. You will find it easier if you take a good old pen and paper and write down the cell references and then reference these individually in your formula. Get a blank (Excel)sheet and play with it and check your references. As far as sorting goes, it is simply brilliant if you can work out how to make it sort as you want (i think 5 IF statements?), your original question is also vauge hence that may be your problem

DeSade
08-09-2009, 10:34 AM
Don't really think its that vague, the others that have answered understand what I am trying to do.

I have 20 data sheets and a formula that counts all the 1's in field D7 on all data sheets the outputs that value to a master sheet for a total. This works fine.

I want a 2nd formula that first checks B7 over all data sheets for a specific name, if that name is present then it does the same as above and totals all the 1's.

So if I had 20 1's and they were split evenly between two names then the first formula would total to 20, the second formula would total to 10.