# counting dependent on date

#### silentcoates

##### New Member
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)``

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?

help please I am so close

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.

hi

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

help me if you can

silentcoates said:
hi

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

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

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.

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?

Replies
2
Views
414
Replies
4
Views
100
Replies
3
Views
240
Replies
2
Views
874
Replies
3
Views
934

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.

### Which adblocker are you using?

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

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