counting dependent on date

[silentcoates]

Hello again,

this is another difficult one to explain, but here goes.

I am producing a spreadsheet that has to produce data on a weekly basis. I need a

formula, if there is one, that can pull information from a sheet between two dates.

example:

in cell A3 I have a list of dates of when the data was entered. In the other columns I

have various bits of data (B - Names, C- a score as a percentage, D - a yes no

value). I need a formula for each bit of data. So on another sheet I need to show

say the average score for a person between two date ranges, on another the number

of yes values between the same dates. I understand I will need to enter slightly

different formulas depandent on the data I am looking at.

Also I need to count the number of times a persons name appears in column B

between two dates.

Hope you can help.

 

[Peter_SSs]

hi

The last post does work. I need this to work on multiple cells that will update automtically as I add new data in the input columns (A,B and c)

using the ctrl, shift and enter does work but how do I do it for all cells?

Do you mean something like this? The formulas are now:

I2: =AVERAGE(IF(($A$3:$A$200> F$2)*($A$3:$A$200< G$2)*($C$3:$C$200<>""),IF($B$3:$B$200=H2,$C$3:$C$200,""))) (Ctrl+Shift+Enter)
J2: =SUMPRODUCT(--($A$3:$A$200>F$2),--($A$3:$A$200<G$2),--($B$3:$B$200=H2),--($D$3:$D$200=J$1))
K2: =SUMPRODUCT(--($A$3:$A$200>F$2),--($A$3:$A$200<G$2),--($B$3:$B$200=H2),--($D$3:$D$200=K$1))
L2: =COUNTIF($B$3:$B$200,H2)

Select I2:L2 and drag the 'Fill Handle' down or copy and paste down. The ranges in the formula have been extended down to row 200 to allow for additional data in columns A:D. You can make these ranges larger or smaller as required. You can add further names in column H and copy the formulas down further as well.

If this is not what you meant, you will have to explain again more clearly.

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1						Date 1	Date 2	Name	Av Score	Yes	No	No. in B
2	Date	Name	Score	Yes/No		09/11/2005	17/11/2005	Sue	56.0%	1	1	5
3	05/11/2005	Fred	10.0%	Yes				Ann	52.7%	2	1	4
4	06/11/2005	Bill	25.0%	No				Bill	25.0%	1	0	3
5	07/11/2005	Sue	68.0%	Yes				Fred	95.2%	1	0	3
6	08/11/2005	Ann	23.0%	Yes
7	09/11/2005	Bill	30.2%	No
8	10/11/2005	Ann	58.4%	Yes
9	11/11/2005	Sue	56.0%	Yes
10	12/11/2005	Sue		No
11	13/11/2005	Fred	95.2%	Yes
12	14/11/2005	Bill	25.0%	Yes
13	15/11/2005	Ann	24.0%	No
14	16/11/2005	Ann	75.8%	Yes
15	17/11/2005	Sue	23.1%	Yes
16	18/11/2005	Sue	23.8%	No
17	19/11/2005	Fred	56.0%	Yes
Count on Date