COUNT VALUES DEPENDING ON DATES

Closed
VE - Aug 19, 2009 at 12:47 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Aug 21, 2009 at 06:44 AM
Hello,

I am trying to count selected values in one column depending on selected date values in a second column.

Example:

The first column contains the values: 1,2,1,12,31,10,1,7,1
The second column contains dates: 5-8-09,12-8-09,13-8-09,4-8-09,8-8-09,14-8-09,16-7-09,15-8-09,6-8-09

I want to count any cell in the first column that contains 1 or 2 but only if the date falls between 6-8-09 and 12-6-09.

The result would be 3.

What formula can I use?

Help please. Thank you.

3 replies

Hi,

Thank you so much, it works!!!

One question though, I should press Ctrl-Shift-Enter rather than Ctrl-Alt-Del at the end?

The '*' between arguments stands for logical 'AND'? MS Help is a bit obscure at times. I couldn't find that anywhere.

Thanks again.

VE
1
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Aug 20, 2009 at 11:52 AM
Use the formula =SUM(($B$1:$B$8<=$A$12)*($B$1:$B$8>=$A$11)*($A$1:$A$8=2))+SUM(($B$1:$B$8<=$A$12)*($B$1:$B$8>=$A$11)*($A$1:$A$8=1)) Press CTRL + ALT+DEL
to enter the formula
$A$12 - end date
$A$11 - start date
0
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Aug 21, 2009 at 06:44 AM
pressing Ctrl+Alt+Del is set to bring up the Windows Security dialog in windows OS
The '*' between arguments stands for multiplication
0