counting dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim Rowlan - 31 Aug 2006 19:01 GMT

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

Reply to this Message

Toppers - 31 Aug 2006 19:24 GMT

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

> 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

Reply to this Message

Tim Rowlan - 31 Aug 2006 19:33 GMT

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,

> =SUMPRODUCT(--(Year(C2:C100)=2006),--(A2:A100="RFP"))
>
[quoted text clipped - 3 lines]
> > Thanks,
> > Tim

Reply to this Message

Pete_UK - 31 Aug 2006 19:39 GMT

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

> 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
[quoted text clipped - 10 lines]
> > > Thanks,
> > > Tim

Reply to this Message

Tim Rowlan - 31 Aug 2006 19:45 GMT

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

> Did you also put the sheet name (plus !) before the range A2:A100?
>
[quoted text clipped - 19 lines]
> > > > Thanks,
> > > > Tim

Reply to this Message

Pete_UK - 31 Aug 2006 19:59 GMT

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

> Pete and Toppers,
>
[quoted text clipped - 27 lines]
> > > > > Thanks,
> > > > > Tim

Reply to this Message