counting dates
 

I have a spreadsheet with many many dates that I need to graph/chart. I want
to chart the number of RFPs I received in each year. How do I count on
column C which is a date column to see the number for any given year?
Thanks,
Tim

counting dates
 

=SUMPRODUCT(--(Year(C2:C100)=2006),--(A2:A100="RFP"))

"Tim Rowlan" wrote:

I have a spreadsheet with many many dates that I need to graph/chart. I want
to chart the number of RFPs I received in each year. How do I count on
column C which is a date column to see the number for any given year?
Thanks,
Tim

counting dates
 

Ok, I copied the formula over and it still returns a value of 0. I have the
data in a seperate sheet, so right before the C2, I added the sheet name and
!. I am not sure what I am doing wrong. Does this formula have to be run on
the sheet where the data is? Also, for my own learning, what are the two --
for?
Thanks,

"Toppers" wrote:

=SUMPRODUCT(--(Year(C2:C100)=2006),--(A2:A100="RFP"))

"Tim Rowlan" wrote:

I have a spreadsheet with many many dates that I need to graph/chart. I want
to chart the number of RFPs I received in each year. How do I count on
column C which is a date column to see the number for any given year?
Thanks,
Tim

counting dates
 

Did you also put the sheet name (plus !) before the range A2:A100?

The -- effectively turns an array of Trues and Falses to an array of
1's and 0's, so they can be counted.

Hope this helps.

Pete

Tim Rowlan wrote:
Ok, I copied the formula over and it still returns a value of 0. I have the
data in a seperate sheet, so right before the C2, I added the sheet name and
!. I am not sure what I am doing wrong. Does this formula have to be run on
the sheet where the data is? Also, for my own learning, what are the two --
for?
Thanks,

"Toppers" wrote:

=SUMPRODUCT(--(Year(C2:C100)=2006),--(A2:A100="RFP"))

"Tim Rowlan" wrote:

I have a spreadsheet with many many dates that I need to graph/chart. I want
to chart the number of RFPs I received in each year. How do I count on
column C which is a date column to see the number for any given year?
Thanks,
Tim

counting dates
 

Pete and Toppers,

Thanks for the information, I took what Toppers wrote and just broke it down
till I found out what I was doing wrong. Pete, yes I did do that, it was a
data error on my part. Thanks for the quick lesson.

Tim

"Pete_UK" wrote:

Did you also put the sheet name (plus !) before the range A2:A100?

The -- effectively turns an array of Trues and Falses to an array of
1's and 0's, so they can be counted.

Hope this helps.

Pete

Tim Rowlan wrote:
Ok, I copied the formula over and it still returns a value of 0. I have the
data in a seperate sheet, so right before the C2, I added the sheet name and
!. I am not sure what I am doing wrong. Does this formula have to be run on
the sheet where the data is? Also, for my own learning, what are the two --
for?
Thanks,

"Toppers" wrote:

=SUMPRODUCT(--(Year(C2:C100)=2006),--(A2:A100="RFP"))

"Tim Rowlan" wrote:

I have a spreadsheet with many many dates that I need to graph/chart. I want
to chart the number of RFPs I received in each year. How do I count on
column C which is a date column to see the number for any given year?
Thanks,
Tim

counting dates
 

Thanks for feeding back, Tim.

I think you'll find this link explains things in a bit more detail:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Pete

Tim Rowlan wrote:
Pete and Toppers,

Thanks for the information, I took what Toppers wrote and just broke it down
till I found out what I was doing wrong. Pete, yes I did do that, it was a
data error on my part. Thanks for the quick lesson.

Tim

"Pete_UK" wrote:

Did you also put the sheet name (plus !) before the range A2:A100?

The -- effectively turns an array of Trues and Falses to an array of
1's and 0's, so they can be counted.

Hope this helps.

Pete

Tim Rowlan wrote:
Ok, I copied the formula over and it still returns a value of 0. I have the
data in a seperate sheet, so right before the C2, I added the sheet name and
!. I am not sure what I am doing wrong. Does this formula have to be run on
the sheet where the data is? Also, for my own learning, what are the two --
for?
Thanks,

"Toppers" wrote:

=SUMPRODUCT(--(Year(C2:C100)=2006),--(A2:A100="RFP"))

"Tim Rowlan" wrote:

I have a spreadsheet with many many dates that I need to graph/chart. I want
to chart the number of RFPs I received in each year. How do I count on
column C which is a date column to see the number for any given year?
Thanks,
Tim