COUNT VALUES DEPENDING ON DATESClosed

Question

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.

Excelguru · Answer

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

VE · Answer

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

Excelguru · Answer

pressing Ctrl+Alt+Del is set to bring up the Windows Security dialog in windows OS
The '*' between arguments stands for multiplication

COUNT VALUES DEPENDING ON DATES

3 responses

Similar discussions

Subscribe To Our Newsletter!