Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I count occurrences of multiple conditions | Excel Worksheet Functions | |||
how do I count the occurrences of multiple conditions | Excel Worksheet Functions | |||
€śCount€ť occurrences of two conditions on the same row | Excel Worksheet Functions | |||
CountIf Conditions / Occurrences | Excel Worksheet Functions | |||
Counting occurrences of multiple conditions | Excel Discussion (Misc queries) |