3 results possible on every day. add up the number of each on each day.

[robzrob]

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

 

[Claus Busch]

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

 

[Claus Busch]

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