I’m pretty good with Excel formula’s, but I’ve picked it up in an ad hoc way in certain areas, and now I’m trying to do a couple of things that I can’t figure out how to do it.
Hoping some Excel guru can rattle off some help.
So basically I have a table set out like this (there’s more to it, but this is the basic layout)
’Team Name’ ‘Race’ ‘TD’s For’ 'TD’s Against’
Each Row represents one game of Fantasy Football (Bloodbowl), and the result of the same, including touchdowns, casualties, etc. Each row is a distinct game but multiple games can be played against particular teams and particular races. meaning more than one row with the same team name and race in it, but different stats.
I have a simple autofilter on the table to let me select certain lines to view. At the bottom of the table on each of the numercial columns I have a simple SUM formula
I’m trying to do one of the following things:
Is it possible to have a formula on the bottom of each numerical column, that when you use the autofilter buttons to narrow down the displayed rows, it only sums the displayed results ?
Either/Or to the above I would like to have a reference cell, which I could type into it a team name and have it total the corresponding numbers in the rows with that team in it. So for example say there were 4 games involving The Raiders, each having its own row, the formula would take the ‘TD’s for’ in each of those rows and total them. I’m presuming this can be achieved by some variation of a Vlookup command, but I can’t figure how to make it do this.
I often create several versions of a worksheet by using the worksheet tabs.
The full sheet might have columns A thru K.
One of my tabs may have data from columns A,C,G,F. I’ll have the headings and formulas preset.
Then, I select the data in the Tab and use Clear Contents. Then go to my main sheet (always the first tab), filter (if needed) and select/copy the data. Paste into the alternate sheet and usually custom sort. Print, give it to my boss and grab a cup of coffee.
It’s not the most elegant way. But, I usually have several projects going at once. I have to get it done and get back to my other work.
Seriously, you can customize your data very easily with tabs.
Pick a cell that you are going to enter the team name into.
Let’s say K2
Put your headers across the row below it in K3, L3, M3 etc.
**‘TD’s For’ ‘TD’s Against’.
**
In K4 enter =SUMIF(A2:A1000, K2, C2:C1000)
Repeat as necessary across the row.
All it says is add up all the values from column C (‘TD’s For’) where the team name in column A is the same as the name in cell K2.
It’s kind of a cliche in these types of threads, but if you don’t already know how, I’d learn how to do a pivot chart. It does exactly what you’re looking to do, and quite a bit more. You wouldn’t have to toggle back and forth between teams - it would give a summary of all team totals at the same time.
I second Munch’s recommendation re Pivot Tables. But if you don’t want to take time to learn them, use the SUBTOTAL(9,range) function. It will only add the unhidden rows.
I used to work with some spreadsheets that utilised Pivot tables, and I picked up a bit about how to use them, but that was 10+ years ago, and I’ve forgotten most of it. looks like I’ll have to pick them up again :smack: