Help with Excel(real quick and easy question)

[sublime]

Yo-

Here is what I need. I track data (bets) over a 4 month period and break it down weeek by week (and sport by sport). To keep it as simple as possible (i am not terribly swift) I just do a simple AutoFilter and sort by week. Heres my question. How do I do a SUM of the filtered data? I cant do a simple (h1:h14) because it grabs data I have no interest in.

[pokerdirty]

http://images.amazon.com/images/P/1568840500.01._SCLZZZZZZZ_.gif

[Soul Daddy]

=Subtotal(9,CellStart:CellEnd)

[STLantny]

Sublime, its actually a complicated thing to do. I have something similar, in that it separets bets using listboxes by sport, but i had to get a custome visual basic script written.

[DMBFan23]

what you can do, although it's manual, is highlight the row after you apply the auto filter and right click the box on the bottom bar in the lower right that says "average = ..." and change it to sum (you can also change it to min, max, count, etc).

[touchfaith]

Help me first.

If I go to the store to buy Top Ramen, and know in the back of my mind that I like eating it uncooked more, which flavor should I buy?

[WackityWhiz]

[ QUOTE ]
Help me first.

If I go to the store to buy Top Ramen, and know in the back of my mind that I like eating it uncooked more, which flavor should I buy?

[/ QUOTE ]

chicken of course

[sublime]

[ QUOTE ]
Help me first.

If I go to the store to buy Top Ramen, and know in the back of my mind that I like eating it uncooked more, which flavor should I buy?

[/ QUOTE ]

Dude that [censored] is awful for you.

[sublime]

[ QUOTE ]
http://images.amazon.com/images/P/1568840500.01._SCLZZZZZZZ_.gif

[/ QUOTE ]

The fact that I dont waste money on books like this probably explains why I am worth about 1000x more than you are /images/graemlins/smile.gif

[pokerdirty]

[ QUOTE ]
The fact that I dont waste money on books like this probably explains why I am worth about 1000x more than you are /images/graemlins/smile.gif

[/ QUOTE ]

http://jira.atlassian.com/secure/attachment/12152/Gold_Star.gif

Congratulations, dick!

[sublime]

[ QUOTE ]
=Subtotal(9,CellStart:CellEnd)

[/ QUOTE ]

Bammo!

Just doing the math longhand to confirm, but this looks like the way to go /images/graemlins/smile.gif

What does the ,9 do?

[sublime]

haha, i was just joshing with ya amigo /images/graemlins/smile.gif

[Soul Daddy]

[ QUOTE ]
[ QUOTE ]
=Subtotal(9,CellStart:CellEnd)

[/ QUOTE ]

Bammo!

Just doing the math longhand to confirm, but this looks like the way to go /images/graemlins/smile.gif

What does the ,9 do?

[/ QUOTE ]
It's the part that tells it to sum the selected data. I think 1 = average, 3 = count. Those are the only ones I know.

[pokerdirty]

[ QUOTE ]
haha, i was just joshing with ya amigo /images/graemlins/smile.gif

[/ QUOTE ]

i wasn't /images/graemlins/smile.gif

[Bulldog]

Instead of autofilter, you could use SUMIF. That will allow you to put conditions on the cells being summed (like that the cell next to it equals "FB" or "BKB" or something).

[TheWorstPlayer]

Easiest way is just to use the highlighting trick mentioned above. Highlight the filtered data and right-click on the status bar at the bottom right of the screen. Select 'Sum' and it will display the sum there. The best thing to do, though, is just to create a summary table on another sheet where you list all the different sports or whatever you want to tally and then use SUMIF functions to populate the table with the data.

For instance, I would have a sheet on table called 'Summary' and then a second sheet called 'Data'. On the summary sheet I would have something like sports going down and stats like 'total bet', 'return', 'win/no win', whatever. Then just use a formula like =SUMIF('Data'!b1:b350,a2,'Data'!c1:c350) in the table to fill in the data dynamically for each sport. If you type "=SUMIF(" in the function bar and then click on the little equals sign to the left of the function bar it will pop up a little help window that should help. Feel free to ask me for clarification if this isn't clear.

[sublime]

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
=Subtotal(9,CellStart:CellEnd)

[/ QUOTE ]

Bammo!

Just doing the math longhand to confirm, but this looks like the way to go /images/graemlins/smile.gif

What does the ,9 do?

[/ QUOTE ]
It's the part that tells it to sum the selected data. I think 1 = average, 3 = count. Those are the only ones I know.

[/ QUOTE ]

Well it worked. Good job man!

[sublime]

what you can do, although it's manual, is highlight the row after you apply the auto filter and right click the box on the bottom bar in the lower right that says "average = ..." and change it to sum (you can also change it to min, max, count, etc).

the simplicity of this went over my head. thanks /images/graemlins/smile.gif

I am now going to dick around with the tricks worst player etc said to try.

thanks /images/graemlins/smile.gif well except pokerdirty. i hope that by the time you pay off your college education your dick stops working for you.

[pokerdirty]

[ QUOTE ]
what you can do, although it's manual, is highlight the row after you apply the auto filter and right click the box on the bottom bar in the lower right that says "average = ..." and change it to sum (you can also change it to min, max, count, etc).

the simplicity of this went over my head. thanks /images/graemlins/smile.gif

I am now going to dick around with the tricks worst player etc said to try.

thanks /images/graemlins/smile.gif well except pokerdirty. i hope that by the time you pay off your college education your dick stops working for you.

[/ QUOTE ]

So, if it's already paid off...

...oh that's not very nice.

[sublime]

I /images/graemlins/heart.gif you

even if you hate me. I am just that kind of guy.

[JordanIB]

A PivotTable will do what you gotta do. So much better than anything else.

Greatest creation ever.

[pokerdirty]

[ QUOTE ]
I /images/graemlins/heart.gif you

even if you hate me. I am just that kind of guy.

[/ QUOTE ]

I feel like this is the XM Radio commercial. Where the Yankees and Red Sox neighbors are looking at each other, and then they finally hug...

[TheWorstPlayer]

[ QUOTE ]
A PivotTable will most likely do what you gotta do. So much worse than anything else.

Least efficient and least flexible creation ever.

[/ QUOTE ]

[sublime]

[ QUOTE ]
[ QUOTE ]
A PivotTable will most likely do what you gotta do. So much worse than anything else.

Least efficient and least flexible creation ever.

[/ QUOTE ]

[/ QUOTE ]

FLAME WAR!!!!!!!!!!

DO IT DO IT DO IT

[JordanIB]

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
A PivotTable will most likely do what you gotta do. So much worse than anything else.

Least efficient and least flexible creation ever.

[/ QUOTE ]

[/ QUOTE ]



FLAME WAR!!!!!!!!!!

DO IT DO IT DO IT

[/ QUOTE ]

/images/graemlins/smile.gif

Too easy for my side. In 20 seconds this guy could have every friggin' look at his bets he could want. All football, only Tuesday football, only baseball on a weekend, and on and on and on.

[TheWorstPlayer]

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
A PivotTable will most likely do what you gotta do. So much worse than anything else.

Least efficient and least flexible creation ever.

[/ QUOTE ]

[/ QUOTE ]



FLAME WAR!!!!!!!!!!

DO IT DO IT DO IT

[/ QUOTE ]

/images/graemlins/smile.gif

Too easy for my side. In 20 seconds this guy could have every friggin' look at his bets he could want. All football, only Tuesday football, only baseball on a weekend, and on and on and on.

[/ QUOTE ]
Yes, I'm sure he'll thoroughly enjoy basing analyses on his pivot table only to realise that he has to a)click 'Refresh Data' all the effing time and b)account for the changing dimensions of the pivot table when he changes the displayed data which can then mess up his formulae. It really sucks when you base your formula on a pivot table and then add a dimension only to realise that it breaks all of your work. Also, the fact that you can only copy-paste formula based off a pivot table if you manually input the formula as 'a5' or whatever, instead of being able to click (which automatically inserts some obscure static GETPIVOTDATA formula).

Bottom line: pivot tables are cool and all for looking at a new data set to get a sense for how things fit together. They really really suck for anything that you will want to use long term.

[JordanIB]

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
A PivotTable will most likely do what you gotta do. So much worse than anything else.

Least efficient and least flexible creation ever.

[/ QUOTE ]

[/ QUOTE ]



FLAME WAR!!!!!!!!!!

DO IT DO IT DO IT

[/ QUOTE ]

/images/graemlins/smile.gif

Too easy for my side. In 20 seconds this guy could have every friggin' look at his bets he could want. All football, only Tuesday football, only baseball on a weekend, and on and on and on.

[/ QUOTE ]

Also, the fact that you can only copy-paste formula based off a pivot table if you manually input the formula as 'a5' or whatever, instead of being able to click (which automatically inserts some obscure static GETPIVOTDATA formula).

[/ QUOTE ]

You can just change that GETPIVOT function to act relative to your reference needs.

[TheWorstPlayer]

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
A PivotTable will most likely do what you gotta do. So much worse than anything else.

Least efficient and least flexible creation ever.

[/ QUOTE ]

[/ QUOTE ]



FLAME WAR!!!!!!!!!!

DO IT DO IT DO IT

[/ QUOTE ]

/images/graemlins/smile.gif

Too easy for my side. In 20 seconds this guy could have every friggin' look at his bets he could want. All football, only Tuesday football, only baseball on a weekend, and on and on and on.

[/ QUOTE ]

Also, the fact that you can only copy-paste formula based off a pivot table if you manually input the formula as 'a5' or whatever, instead of being able to click (which automatically inserts some obscure static GETPIVOTDATA formula).

[/ QUOTE ]

You can just change that GETPIVOT function to act relative to your reference needs.

[/ QUOTE ]
Yes, but that means changing each formula. You can't copy-paste it down a row or column to base of off anything dynamic since it is inherently static.

[RacersEdge]

2 words: pivot tables

[TheWorstPlayer]

[ QUOTE ]
2 words: pivot tables

[/ QUOTE ]
Dude, you can't just jump into a flame war like that. [censored].

[Uglyowl]

The SUMIF function is very helpful (as pointed out in an earlier post) in excel and especially in your case. You could probably do some pretty neat stuff you didn't expect you could.

Any questions, I would be happy to help. Sounds like you have the problem solved at least for now.

[TheWorstPlayer]

[ QUOTE ]
The SUMIF function is very helpful

[/ QUOTE ]
This is true, and although I recommended it also, perhaps it should be pointed out that if astro will be doing TONS of sports betting (when he quits his IT job, I guess) SUMIF is very inefficient. It is better to use SUM(IF()) in array formulas. That is also the most flexible construction.