Counting occurrences with conditions
 

Hello -

I am trying to calculate how many appointments that we planned to hold were
actually held within 4 weeks of the planned date.

For example, my input data is -
Col A Col B Col C
1. Planned Actual Within 4 weeks?
2. 01-Aug 01-Aug TRUE
3. 03-Aug 10-Aug TRUE
4. 05-Aug 03-Sep FALSE
5. 01-Sep 05-Sep TRUE
6. 05-Sep 10-Sep TRUE

Col C is calculated as =IF(B2<A2+28,TRUE,FALSE)

My Output Data is supposed to be

Col B Col C
Appointments
Actually Held Number Within 4 weeks
9. August 2 2
10. September 3 2

Column B =SUMPRODUCT(--(MONTH($B$2:$B$6)=8))

So far, so good.

I am not sure how I can count the number of appointment actually held within
4 weeks.

Does anyone have any suggestions or pointers?

Many thanks
Neil

Counting occurrences with conditions
 

Using your helper column c

=SUMPRODUCT((MONTH($a$1:$a$21)=8)*($c$1:$c$21=FALS E))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Neil Perry" wrote in message
...
Hello -

I am trying to calculate how many appointments that we planned to hold
were
actually held within 4 weeks of the planned date.

For example, my input data is -
Col A Col B Col C
1. Planned Actual Within 4 weeks?
2. 01-Aug 01-Aug TRUE
3. 03-Aug 10-Aug TRUE
4. 05-Aug 03-Sep FALSE
5. 01-Sep 05-Sep TRUE
6. 05-Sep 10-Sep TRUE

Col C is calculated as =IF(B2<A2+28,TRUE,FALSE)

My Output Data is supposed to be

Col B Col C
Appointments
Actually Held Number Within 4 weeks
9. August 2 2
10. September 3 2

Column B =SUMPRODUCT(--(MONTH($B$2:$B$6)=8))

So far, so good.

I am not sure how I can count the number of appointment actually held
within
4 weeks.

Does anyone have any suggestions or pointers?

Many thanks
Neil

Counting occurrences with conditions
 

Don -

That's great, thanks for your help. I am not sure I understand how it works,
but it is the result I need!

Thanks again
Neil

"Don Guillett" wrote:

Using your helper column c

=SUMPRODUCT((MONTH($a$1:$a$21)=8)*($c$1:$c$21=FALS E))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Neil Perry" wrote in message
...
Hello -

I am trying to calculate how many appointments that we planned to hold
were
actually held within 4 weeks of the planned date.

For example, my input data is -
Col A Col B Col C
1. Planned Actual Within 4 weeks?
2. 01-Aug 01-Aug TRUE
3. 03-Aug 10-Aug TRUE
4. 05-Aug 03-Sep FALSE
5. 01-Sep 05-Sep TRUE
6. 05-Sep 10-Sep TRUE

Col C is calculated as =IF(B2<A2+28,TRUE,FALSE)

My Output Data is supposed to be

Col B Col C
Appointments
Actually Held Number Within 4 weeks
9. August 2 2
10. September 3 2

Column B =SUMPRODUCT(--(MONTH($B$2:$B$6)=8))

So far, so good.

I am not sure how I can count the number of appointment actually held
within
4 weeks.

Does anyone have any suggestions or pointers?

Many thanks
Neil