Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 results possible on every day. add up the number of each on each day.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 results possible on every day. add up the number of each on each day.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 results possible on every day. add up the number of each on each day.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking the results of a number of IF formula | Excel Worksheet Functions | |||
HOW DO I CALCULATE THE NUMBER OF RESULTS FROM AN ENTIRE ROW | Excel Worksheet Functions | |||
Vlookup results in a number? | Excel Discussion (Misc queries) | |||
Negative number results to show as zero | Excel Discussion (Misc queries) | |||
random number results | Excel Worksheet Functions |