AutoTabulate

[Guest]

Hi All,
I would like to do a auto tabulate formula for this problem,
rather than i go calucate from time to time.

Let's say if i have a table like this:
No Game Type Hours
--- ------ ----- ------
1 swim outdoor 2
2 jog indoor 1.5
3 swim outdoor 1
4 bball both 3
5 vball indoor 1.5
6 vball indoor 1
7 tennis outdoor 2

With the above table, the excel will auto give me a drop down (i dont know
what does this called), it will help me to group under
1) jog, swim bball, vball & tennis. 2) indoor,outdoor & both. 3) 0.25,1,3 &
1.5
________________________________________________________________

Now i trying to create a calculation by having this:

Total | Swim | Jog | BBall | vBall | Tennis
count 7 2 1 1 2 1
Hours 12 3 1.5 3 2.5 2

If i using formula inside the fx=count for Total, it will be simple.
But how do i calculate for other like Swim, Jog, etc


THanks~

 

[Dave Peterson]

=counta(b2:b9999)
will give you the total number of cells in B2:B9999 that have anything in it.

=sum(d2:d9999)
will sum the total hours in D29999

=countif(b2:b9999,"swim")
will count the number of cells that are equal to "swim"

=sumif(b2:b9999,"swim",d2:d9999)
will sum the values in D29999 where the value in column B (same row) is equal
to "swim"

You may want to learn more about Data|Pivottables (xl2003 menu system). It
could make this kind of summary report lots easier.

 

[Guest]

where can i find more on Data|Pivottables (xl2003 menu system)?

between if i have data like this:
ID No Game Type Hours
--- ------ ----- ------
S0910 1 swim outdoor 2
S0911 2 jog indoor 1.5
S0912 3 swim outdoor 1
DR1810 4 bball both 3
DR1811 5 vball indoor 1.5
DX0010 6 vball indoor 1
DX2000 7 tennis outdoor 2

Now can i do a count by ID (first 4 letters)
Mean by S091 will give 3, DR18 will give 2 and DX00 will give 1 and DX20
will give 1.

Can this be dong?

 

[Dave Peterson]

I'd add a new column based on the ID column. I'd use formulas like:

=left(a2,4)
(and drag down)

Then give that column a nice header so that I could use it in the pivottable.

Just drag that header into the data field and you should see the "count of
IDAbbrev".