Hello everyone,
I would appreciate anyone's help with 2 formulas.
Formula in Column “G”= IF(COUNTIF($C$2:c2,C2)=1,1,””). It counts unique names of Clients for the period
Reflected in Column “A”, who received services GFAP(Column E) in the period in Column “D”. Counts only once the name.(E.G> ABANOedgaro)
Formula in Column “H”=IF(AND(D2>=A2,(COUNTIF($C$2:C2,C2)=1)). It counts New clients in the period in Column A who got “First Service” in column “D” which matches the period in column “A”.
NEED formula in column “I”. It counts clients received services in Current month Column “D”, who were new patients one month before. E.G. if we count new clients in April 2015(column A), so P2 are new clients from March.
NEED formula in column “J”. It counts clients received services in Current month Column “D”, who were new patients one monthbefore. E.G. if we count new clients in April 2015(column A), so P3 are new clients from February.
<colgroup><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
I would appreciate anyone's help with 2 formulas.
Formula in Column “G”= IF(COUNTIF($C$2:c2,C2)=1,1,””). It counts unique names of Clients for the period
Reflected in Column “A”, who received services GFAP(Column E) in the period in Column “D”. Counts only once the name.(E.G> ABANOedgaro)
Formula in Column “H”=IF(AND(D2>=A2,(COUNTIF($C$2:C2,C2)=1)). It counts New clients in the period in Column A who got “First Service” in column “D” which matches the period in column “A”.
NEED formula in column “I”. It counts clients received services in Current month Column “D”, who were new patients one month before. E.G. if we count new clients in April 2015(column A), so P2 are new clients from March.
NEED formula in column “J”. It counts clients received services in Current month Column “D”, who were new patients one monthbefore. E.G. if we count new clients in April 2015(column A), so P3 are new clients from February.
| A | B | C | D | E | F | G | H | I | J |
| Date | Location | First,Last Name | ymd_first_service | gfap | amount | Unique Names of Clients | NEW Clients | Period 2 | Period 3 |
| Jan-15 | Company1 | ABADagnes | 11/17/14 | PT/Physio | 140 | .=IF(COUNTIF($C$2:C2,C2)=1,1,"") | .=IF(AND(D2>=A2,(COUNTIF($C$2:C2,C2)=1)),1,"") | Need formula | Need formula |
| Jan-15 | Company2 | ABANOedgardo | 11/22/13 | Comp Stock/ Pdts | 400 | 1 | |||
| Jan-15 | Company3 | ABANOedgardo | 11/22/13 | DC/Admin | 95 | ||||
| Feb-15 | Company1 | ABANOedgardo | 11/22/13 | Orthotics | 300 | ||||
| Feb-15 | Company2 | ABANOedgardo | 11/22/13 | RMT | 55 | ||||
| Feb-15 | Company3 | ABBAIsrividhyavathi | 5/5/08 | DC/Admin | 240 | 1 | |||
| Feb-15 | Company4 | ABBASIfrances | 5/8/14 | PT/Physio | 1560 | 1 | |||
| Feb-15 | Company5 | ABBATIELLOjonathan | 8/14/14 | DC/Admin | 45 | 1 | |||
| Mar-15 | Company1 | ABDALLAjamil | 10/28/14 | RMT | 90 | 1 | |||
| Mar-15 | Company2 | ABDELRAZEKnermean | 1/19/15 | DC/Admin | 110 | 1 | |||
| Mar-15 | Company3 | ABDELRAZEKnermean | 1/19/15 | PT/Physio | 150 | ||||
| Apr-15 | Company1 | ABDOOLkim | 2/16/13 | DC/Admin | 35 | 1 | |||
| Apr-15 | Company2 | ABDUL-HAKIMhusain | 9/23/14 | DC/Admin | 0 | 1 | |||
| Apr-15 | Company3 | ABDUL-HAKIMhusain | 1/29/15 | DC/Admin | 111 | ||||
| Apr-15 | Company4 | ABDUL-HAKIMhusain | 9/23/14 | PT/Physio | 98.86 | ||||
| May-15 | Company2 | ABECASSISmarcos | 11/24/14 | MVA | 1077.4 | 1 | |||
| May-15 | Company3 | ABENOJAcharina | 1/14/15 | DC/Admin | 95 | 1 | |||
| May-15 | Company4 | ABENOJAcharina | 1/14/15 | PT/Physio | 355 | ||||
| May-15 | Company5 | ABITRANTElorena | 4/1/14 | PT/Physio | 195 | 1 |
<colgroup><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
)
