• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif formula

D

Dan_K_2014

Member
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 month before. 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
 

Attachments

  • Sent to MRExcel_20150528.xlsx
    11.4 KB · Views: 0
Last edited:
Hi ,

It would make it easier for others to help , if you could put all of this posted data and formulae in a workbook , and upload the workbook.

Narayan
 
Hi Narayan,

when i go to upload a file, it does not give me an option to see Excel files..and I cannot upload a file.
 
Hi Jake,

Thank you for your trying... Formula should count clients in column "I" who received service in the month and Year reflected on Column 'A", but those who received their first service one month earlier. For example, if we see in column "A" January, 2015- it means that we need to count clients from column "C" only those who received their first service in December 2014...and so on.

An accordingly in column "J" to count those clients who have received their first service in November 2014...

Because of different number of days in the month (28,29,30,31) we cannot write (A2-30...) the formula should be Dynamic...

In your formula if i take =month(A2)-1 gives Jan-00 it should give Dec-2014...
 
For example, with existing data There should not be no counts in column "I",
in column "J" - there is 1 in J2, because client AbaDagnes in January 2015 received service and he was a new(received first service) client in November 2014.
 
Hi Dan,

I think @Jake Collins formula will give correct result. An alternative to that formula is as given below:

IN I3 and copy down:
=IF(AND(D3>=EDATE(A3,-1),COUNTIF(C$3:C3,C3)=1),1,0)
In J3 and copy down:
=IF(AND(D3>=EDATE(A3,-2),COUNTIF(C$3:C3,C3)=1),1,0)

Regards,
 
Hi SM,

thank you for trying... It does not work... formula counts "New clients" also for the same month.. If A3=January 2015 and in column "D" January 2015 it counts, but it should count ONLY December 2014 in "D"...
 
HI Sm,
IN I3 may be shoud be something like this? to show the period not more than 1 Month less than in A3...
.=IF(AND(D3>=EDATE(A3,-1),D3<=EDATE(A3,-2),COUNTIF(C$3:C3,C3)=1),1,0)
 
You must log in or register to reply here.
Back
Top