Breaking down a cell to create a function
 

I'm trying to create a flow graph by using current information on my
spreadsheet. I have a column of dates in this format 01/05/06. I trying to
find a formula that will only use the year in that format, so I can show
trends by the year. Please help! I've been trying this for a week and can't
figure it out. And I don't even know if it's possible.

Breaking down a cell to create a function
 

Hi,

You don't actually say what you want to do but this takes the year from
dates in column A and compares them to the year of a date in C1 and if the
same sums column B

=SUMPRODUCT((YEAR(A1:A13)=YEAR(C1))*(B1:B13))

Mike

"Robert" wrote:

I'm trying to create a flow graph by using current information on my
spreadsheet. I have a column of dates in this format 01/05/06. I trying to
find a formula that will only use the year in that format, so I can show
trends by the year. Please help! I've been trying this for a week and can't
figure it out. And I don't even know if it's possible.

Breaking down a cell to create a function
 

=YEAR(A1)

"Robert" wrote:

I'm trying to create a flow graph by using current information on my
spreadsheet. I have a column of dates in this format 01/05/06. I trying to
find a formula that will only use the year in that format, so I can show
trends by the year. Please help! I've been trying this for a week and can't
figure it out. And I don't even know if it's possible.

Breaking down a cell to create a function
 

I have a column of over 700 of multiple dates in this format mm/dd/yy ie
6/15/07. I want to count all the years. Like 05, 06, 07, 08, 09 and create a
flow chart from the with in the information. My columns looks something like
this:
8/9/06
7/16/09
6/15/05
1/12/08
1/26/07
6/1/08

I want a function that only recognizes the year in that format, so I can
count how many 05's, 06's, 07's, 08's I have so I can chart the different
trends in years.
Thanks for the help.


"Mike H" wrote:

Hi,

You don't actually say what you want to do but this takes the year from
dates in column A and compares them to the year of a date in C1 and if the
same sums column B

=SUMPRODUCT((YEAR(A1:A13)=YEAR(C1))*(B1:B13))

Mike

"Robert" wrote:

I'm trying to create a flow graph by using current information on my
spreadsheet. I have a column of dates in this format 01/05/06. I trying to
find a formula that will only use the year in that format, so I can show
trends by the year. Please help! I've been trying this for a week and can't
figure it out. And I don't even know if it's possible.

Breaking down a cell to create a function
 

=sumproduct(--(year(a1:a100)=2000))
(four digit years!)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=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


Robert wrote:

I have a column of over 700 of multiple dates in this format mm/dd/yy ie
6/15/07. I want to count all the years. Like 05, 06, 07, 08, 09 and create a
flow chart from the with in the information. My columns looks something like
this:
8/9/06
7/16/09
6/15/05
1/12/08
1/26/07
6/1/08

I want a function that only recognizes the year in that format, so I can
count how many 05's, 06's, 07's, 08's I have so I can chart the different
trends in years.
Thanks for the help.


"Mike H" wrote:

Hi,

You don't actually say what you want to do but this takes the year from
dates in column A and compares them to the year of a date in C1 and if the
same sums column B

=SUMPRODUCT((YEAR(A1:A13)=YEAR(C1))*(B1:B13))

Mike

"Robert" wrote:

I'm trying to create a flow graph by using current information on my
spreadsheet. I have a column of dates in this format 01/05/06. I trying to
find a formula that will only use the year in that format, so I can show
trends by the year. Please help! I've been trying this for a week and can't
figure it out. And I don't even know if it's possible.

--

Dave Peterson

Breaking down a cell to create a function
 

Assuming your data from a2:a7, criteria in start in b2

In C2: =SUMPRODUCT(--(TEXT($A$2:$A$7,"yy")=B2))

copy down



"Robert" wrote:

I have a column of over 700 of multiple dates in this format mm/dd/yy ie
6/15/07. I want to count all the years. Like 05, 06, 07, 08, 09 and create a
flow chart from the with in the information. My columns looks something like
this:
8/9/06
7/16/09
6/15/05
1/12/08
1/26/07
6/1/08

I want a function that only recognizes the year in that format, so I can
count how many 05's, 06's, 07's, 08's I have so I can chart the different
trends in years.
Thanks for the help.


"Mike H" wrote:

Hi,

You don't actually say what you want to do but this takes the year from
dates in column A and compares them to the year of a date in C1 and if the
same sums column B

=SUMPRODUCT((YEAR(A1:A13)=YEAR(C1))*(B1:B13))

Mike

"Robert" wrote:

I'm trying to create a flow graph by using current information on my
spreadsheet. I have a column of dates in this format 01/05/06. I trying to
find a formula that will only use the year in that format, so I can show
trends by the year. Please help! I've been trying this for a week and can't
figure it out. And I don't even know if it's possible.