Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.


For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:


Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------


any way just using the excel inside or do i have to use visua basic?

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



kyoshirou wrote:

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

i trying to use this:
=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)),


but i have many date from 01-aug-00, 02-sep-00. 10-jan-00 to year 2006.
therefore i would like to finalise by year, maybe 00. But how do i set it
inside the date as "year2000" or "00"?
Please teach me. thanks.


=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)), change the "June"
Meaning the "June" is change to year. But cant set to "year00" or "01-01-00".

__________________________________________________ ________________
"Dave Peterson" wrote:

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



kyoshirou wrote:

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

You may want to try the =sumproduct() suggestion if you're going to eschew the
pivottable.

kyoshirou wrote:

i trying to use this:
=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)),

but i have many date from 01-aug-00, 02-sep-00. 10-jan-00 to year 2006.
therefore i would like to finalise by year, maybe 00. But how do i set it
inside the date as "year2000" or "00"?
Please teach me. thanks.

=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)), change the "June"
Meaning the "June" is change to year. But cant set to "year00" or "01-01-00".

__________________________________________________ ________________
"Dave Peterson" wrote:

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



kyoshirou wrote:

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

i trying to add up the total marks. but how come i dont need to use the
"Mark" for the forumla?

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

i trying to solve this first b4 i using pivottable, thereafter see which is
better.
thanks.

"Dave Peterson" wrote:

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

You may want to try the =sumproduct() suggestion if you're going to eschew the
pivottable.

kyoshirou wrote:

i trying to use this:
=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)),

but i have many date from 01-aug-00, 02-sep-00. 10-jan-00 to year 2006.
therefore i would like to finalise by year, maybe 00. But how do i set it
inside the date as "year2000" or "00"?
Please teach me. thanks.

=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)), change the "June"
Meaning the "June" is change to year. But cant set to "year00" or "01-01-00".

__________________________________________________ ________________
"Dave Peterson" wrote:

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



kyoshirou wrote:

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You're right:

=sumproduct(--(year(b1:b100)=2000),--(a1:a100="ford"),c1:c100)



kyoshirou wrote:

i trying to add up the total marks. but how come i dont need to use the
"Mark" for the forumla?

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

i trying to solve this first b4 i using pivottable, thereafter see which is
better.
thanks.

"Dave Peterson" wrote:

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

You may want to try the =sumproduct() suggestion if you're going to eschew the
pivottable.

kyoshirou wrote:

i trying to use this:
=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)),

but i have many date from 01-aug-00, 02-sep-00. 10-jan-00 to year 2006.
therefore i would like to finalise by year, maybe 00. But how do i set it
inside the date as "year2000" or "00"?
Please teach me. thanks.

=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)), change the "June"
Meaning the "June" is change to year. But cant set to "year00" or "01-01-00".

__________________________________________________ ________________
"Dave Peterson" wrote:

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



kyoshirou wrote:

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

using this: =sumproduct(--(year(b1:b100)=2000),--(a1:a100="ford"),c1:c100)

Adjust the ranges to match--but you can't use whole columns

How do i adjust the range to match?
What do i need to match?
Between the 2000 represent year 2000 or year itself will be able to justify
whatever years?






"Dave Peterson" wrote:

You're right:

=sumproduct(--(year(b1:b100)=2000),--(a1:a100="ford"),c1:c100)



kyoshirou wrote:

i trying to add up the total marks. but how come i dont need to use the
"Mark" for the forumla?

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

i trying to solve this first b4 i using pivottable, thereafter see which is
better.
thanks.

"Dave Peterson" wrote:

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

You may want to try the =sumproduct() suggestion if you're going to eschew the
pivottable.

kyoshirou wrote:

i trying to use this:
=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)),

but i have many date from 01-aug-00, 02-sep-00. 10-jan-00 to year 2006.
therefore i would like to finalise by year, maybe 00. But how do i set it
inside the date as "year2000" or "00"?
Please teach me. thanks.

=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)), change the "June"
Meaning the "June" is change to year. But cant set to "year00" or "01-01-00".

__________________________________________________ ________________
"Dave Peterson" wrote:

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



kyoshirou wrote:

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

If you want to use rows 1:10, then you'd make it b1:b10, a1:a10, c1:c10.

And yes, you'll have to change the formula.

Or point at a cell that contains the year.

kyoshirou wrote:

using this: =sumproduct(--(year(b1:b100)=2000),--(a1:a100="ford"),c1:c100)

Adjust the ranges to match--but you can't use whole columns

How do i adjust the range to match?
What do i need to match?
Between the 2000 represent year 2000 or year itself will be able to justify
whatever years?

"Dave Peterson" wrote:

You're right:

=sumproduct(--(year(b1:b100)=2000),--(a1:a100="ford"),c1:c100)



kyoshirou wrote:

i trying to add up the total marks. but how come i dont need to use the
"Mark" for the forumla?

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

i trying to solve this first b4 i using pivottable, thereafter see which is
better.
thanks.

"Dave Peterson" wrote:

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0) )

You may want to try the =sumproduct() suggestion if you're going to eschew the
pivottable.

kyoshirou wrote:

i trying to use this:
=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)),

but i have many date from 01-aug-00, 02-sep-00. 10-jan-00 to year 2006.
therefore i would like to finalise by year, maybe 00. But how do i set it
inside the date as "year2000" or "00"?
Please teach me. thanks.

=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)), change the "June"
Meaning the "June" is change to year. But cant set to "year00" or "01-01-00".

__________________________________________________ ________________
"Dave Peterson" wrote:

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



kyoshirou wrote:

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson