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]

silentcoates

I've done this in a bit of a rush, so haven't given much thought to simpler ways, or much testing, but this might get things started for you.

I2:

=SUMPRODUCT(--($A$3:$A$20>F2),--($A$3:$A$20<G2),--($B$3:$B$20=H2),$C$3:$C$20)/SUMPRODUCT(--($A$3:$A$20>F2),--($A$3:$A$20<G2),--($B$3:$B$20=H2))

J2:

=SUMPRODUCT(--($A$3:$A$20>F2),--($A$3:$A$20<G2),--($B$3:$B$20=H2),--($D$3:$D$20=J1))

K2:

=SUMPRODUCT(--($A$3:$A$20>F2),--($A$3:$A$20<G2),--($B$3:$B$20=H2),--($D$3:$D$20=K1))

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J	K
1						Date 1	Date 2	Name	Av Score	Yes	No
2	Date	Name	Score	Yes/No		09/11/2005	17/11/2005	Sue	71.5%	1	1
3	05/11/2005	Fred	10.0%	Yes
4	06/11/2005	Bill	25.0%	No
5	07/11/2005	Sue	68.0%	Yes
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	87.0%	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
18	20/11/2005	Bill	91.0%	Yes
19	21/11/2005	Ann	2.2%	No
20	22/11/2005	Ann	22.0%	Yes
Count on Date

Edit:
Forgot about the number of times name occurs in column B, so in L2 try:

=COUNTIF($B$3:$B$20,H2)

 

[silentcoates]

Hi

These work a treat thanks.

One slight amendment I need the formula you have given me to input in I2 not to include blank cells as this affects the average score. Is this possible?

 

[silentcoates]

help please I am so close

 

[Aladin Akyurek]

Hi

These work a treat thanks.

One slight amendment I need the formula you have given me to input in I2 not to include blank cells as this affects the average score. Is this possible?

A more adquate formula for a conditional average is:

=AVERAGE(IF(($A$3:$A$20 > F2)*($A$3:$A$20 < G2),IF($B$3:$B$20=H2,$C$3:$C$20,"")))

which must be confirmed with control+shift+enter.

 

[silentcoates]

hi

I cant get this one to work, quite a newbie at this

 

[silentcoates]

help me if you can

 

[Brian from Maui]

hi

I cant get this one to work, quite a newbie at this

What doesn't work. Can you be a little more specific?

 

[Peter_SSs]

Hi

These work a treat thanks.

One slight amendment I need the formula you have given me to input in I2 not to include blank cells as this affects the average score. Is this possible?

Working with Aladin's formula, I think this should exclude blank entries in column C from the average calculation.

=AVERAGE(IF(($A$3:$A$20 > F2)*($A$3:$A$20 < G2)*($C$3:$C$20<>""),IF($B$3:$B$20=H2,$C$3:$C$20,"")))

You mentioned that you couldn't get Aladin's formula to work. Just to spell it out in detail, after you type or paste his fomula into the relevant cell, select the cell and press F2 to put the cell in 'edit' mode. Now while holding CTRL and SHIFT keys down, press ENTER.

 

[silentcoates]

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?