Hi everyone,
Happy Monday to you all. I need to build on the formulas that were created last week for counting visit statuses per location (with many thanks to you for help!) - Now the need is to count the number of visits that fall within a date range for each unique location. For example, with the following data on Tab 2:
<tbody>
</tbody>
We would need an output on Tab1 of:
<tbody>
</tbody>
My existing formulas are:
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="*"))
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="#"))
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="^"))
In this new calculation, status doesn't matter, so I have
1. just taken one of the formulas above: =SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="#"))
2. and would like to add a search for cells that contain the string "Site": SUMPRODUCT(--ISNUMBER(SEARCH({"site"},Tab 2'!$A1:$H1)))>0)
3. and also the condition that the dates must fall within a date range to be counted: IF('Tab 2'!C1:H10>=DATE(2014,03,01)) AND ('Tab 2'!C1:H10<=DATE(2014,03,31)) (and the same for ranges of April 1 - April 30, May 1 - May 31 and June 1 and June 30)
How can I combine these correctly?
Thanks for any help you can provide!
Natasha
Happy Monday to you all. I need to build on the formulas that were created last week for counting visit statuses per location (with many thanks to you for help!) - Now the need is to count the number of visits that fall within a date range for each unique location. For example, with the following data on Tab 2:
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Location | ID | Site Visit 1 | Visit 1 Status | Site Visit 2 | Visit 2 Status | Site Visit 3 | Visit 3 Status | Location | Nbr of ^ | Nbr of # | Nbr of * | |
| 2 | 1 | ABCD-123 | 1-Mar-14 | ^ | 5-Apr-14 | # | 10-May-14 | * | 1 | 3 | 3 | 3 | |
| 3 | 1 | ABCD-456 | 1-Mar-14 | # | 10-May-14 | # | 4-July-14 | * | 2 | 4 | 4 | 1 | |
| 4 | 1 | ABCD-789 | 5-Jan-14 | ^ | 4-Feb-14 | ^ | 10-Mar-14 | * | 3 | 2 | 3 | 4 | |
| 5 | 2 | WXYZ-098 | 3-Feb-14 | ^ | 10-Mar-14 | # | 15-May-14 | # | |||||
| 6 | 2 | WXYZ-765 | 6-Apr-14 | # | 1-May-14 | ^ | 4-June-14 | # | |||||
| 7 | 2 | WXYZ-432 | 10-Mar-14 | * | 1-May-14 | ^ | 4-June-14 | ^ | |||||
| 8 | 3 | LMNO-123 | 16-Apr-14 | # | 15-June-14 | * | 15-July-14 | # | |||||
| 9 | 3 | LMNO-456 | 1-Mar-14 | * | 1-Apr-14 | ^ | 5-May-14 | * | |||||
| 10 | 3 | LMNO-789 | 10-Apr-14 | ^ | 1-May-14 | # | 15-June-14 | * | |||||
<tbody>
</tbody>
We would need an output on Tab1 of:
| Location | Site Visits in Mar 2014 | Site visits in April 2014 | Site visits in May 2014 | Site visits in June 2014 |
| 1 | 3 | 1 | 2 | |
| 2 | 2 | 1 | 3 | 2 |
| 3 | 1 | 3 | 2 | 2 |
<tbody>
</tbody>
My existing formulas are:
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="*"))
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="#"))
=SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="^"))
In this new calculation, status doesn't matter, so I have
1. just taken one of the formulas above: =SUMPRODUCT(('Tab 2'!$A2:$A10=A2)*('Tab 2'!$C$2:$G$10="#"))
2. and would like to add a search for cells that contain the string "Site": SUMPRODUCT(--ISNUMBER(SEARCH({"site"},Tab 2'!$A1:$H1)))>0)
3. and also the condition that the dates must fall within a date range to be counted: IF('Tab 2'!C1:H10>=DATE(2014,03,01)) AND ('Tab 2'!C1:H10<=DATE(2014,03,31)) (and the same for ranges of April 1 - April 30, May 1 - May 31 and June 1 and June 30)
How can I combine these correctly?
Thanks for any help you can provide!
Natasha
