counting dependent on date

silentcoates

New Member
Joined
Oct 11, 2005
Messages
41
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:
Code:
=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:
Code:
=SUMPRODUCT(--($A$3:$A$20>F2),--($A$3:$A$20<G2),--($B$3:$B$20=H2),--($D$3:$D$20=J1))

K2:
Code:
=SUMPRODUCT(--($A$3:$A$20>F2),--($A$3:$A$20<G2),--($B$3:$B$20=H2),--($D$3:$D$20=K1))
Mr Excel.xls
ABCDEFGHIJK
1Date 1Date 2NameAv ScoreYesNo
2DateNameScoreYes/No09/11/200517/11/2005Sue71.5%11
305/11/2005Fred10.0%Yes
406/11/2005Bill25.0%No
507/11/2005Sue68.0%Yes
608/11/2005Ann23.0%Yes
709/11/2005Bill30.2%No
810/11/2005Ann58.4%Yes
911/11/2005Sue56.0%Yes
1012/11/2005Sue87.0%No
1113/11/2005Fred95.2%Yes
1214/11/2005Bill25.0%Yes
1315/11/2005Ann24.0%No
1416/11/2005Ann75.8%Yes
1517/11/2005Sue23.1%Yes
1618/11/2005Sue23.8%No
1719/11/2005Fred56.0%Yes
1820/11/2005Bill91.0%Yes
1921/11/2005Ann2.2%No
2022/11/2005Ann22.0%Yes
Count on Date


Edit:
Forgot about the number of times name occurs in column B, so in L2 try:
Code:
=COUNTIF($B$3:$B$20,H2)
 
Upvote 0
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?
 
Upvote 0
silentcoates said:
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.
 
Upvote 0
silentcoates said:
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.
Code:
=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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,212,101
Messages
6,105,961
Members
447,987
Latest member
rasika_99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top