Hello

Tried this on the functions board, no reply, think it might need a
macro. Sheet1 Col A is all dates (dd-mm-yy), some of them occurring
more than once, some dates not occurring at all, not in any order and
the earliest date is 20-12-10 (20 Dec 10). Sheet 1 Col H could have
Y, R or CN or other things or nothing in it (but it's only the Ys, Rs
and CNs I'm interested in.) Sheet 4 Col A is a calendar with the
start date 20-12-10 up to whatever today's date is (and beyond as time
goes on.) In Sheet 4 Col B I want the number of Ys of the
corresponding date in Col A, in Col C the number of Rs and in Col D
the number of CNs.

Thanks

Hello,

Am Mon, 23 Jan 2012 13:26:21 -0800 (PST) schrieb robzrob:

> Tried this on the functions board, no reply, think it might need a
> macro. Sheet1 Col A is all dates (dd-mm-yy), some of them occurring
> more than once, some dates not occurring at all, not in any order and
> the earliest date is 20-12-10 (20 Dec 10). Sheet 1 Col H could have
> Y, R or CN or other things or nothing in it (but it's only the Ys, Rs
> and CNs I'm interested in.) Sheet 4 Col A is a calendar with the
> start date 20-12-10 up to whatever today's date is (and beyond as time
> goes on.) In Sheet 4 Col B I want the number of Ys of the
> corresponding date in Col A, in Col C the number of Rs and in Col D
> the number of CNs.

in B1:
=SUMPRODUCT(--(Sheet1!A1:A200=A1),--(Sheet1!H1:H200="Y"))
In C1:
=SUMPRODUCT(--(Sheet1!A1:A200=A1),--(Sheet1!H1:H200="R"))
in D1:
=SUMPRODUCT(--(Sheet1!A1:A200=A1),--(Sheet1!H1:H200="CN"))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hello,

Am Mon, 23 Jan 2012 22:45:50 +0100 schrieb Claus Busch:

> in B1:
> =SUMPRODUCT(--(Sheet1!A1:A200=A1),--(Sheet1!H1:H200="Y"))

change the relative ranges to absolute ranges:
=SUMPRODUCT(--(Sheet1!$A$1:$A$200=A1),--(Sheet1!$H$1:$H$200="Y"))



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2