SOLVED
Home

Count of people on a ward for a list of dates

%3CLINGO-SUB%20id%3D%22lingo-sub-666653%22%20slang%3D%22en-US%22%3ECount%20of%20people%20on%20a%20ward%20for%20a%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666653%22%20slang%3D%22en-US%22%3E%3CP%3EMorning%20everyone%3C%2FP%3E%3CP%3EI'm%20using%20Office%2FExcel%202010%20and%20have%20a%20long%20list%20of%20patients%20with%20a%20date%20they%20arrived%20on%20a%20ward%20and%20the%20date%20the%20left%20the%20ward%3B%20what%20i%20need%20is%20a%20count%20of%20all%20the%20people%20that%20are%20on%20the%20ward%20each%20day.%26nbsp%3B%20In%20the%20excerpt%20below%2C%20I%20can%20see%20that%20on%20the%202nd%20of%20April%26nbsp%3B%20there%20were%204%20(325%2C%20327%2C%20330%2C%20331)%20who%20were%20already%20on%20the%20ward%20and%20another%204%20(332%2C333%2C334%2C335)%20who%20were%20admitted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20asking%20colleagues%20and%20Dr%20Google%20and%20had%20a%20go%20with%20the%20following%20formula%20which%20gives%209%20for%20some%20reason%3A%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((A2%26gt%3B%24I%245%3A%24I%24537)*1%2C(A2%26lt%3B%24J%245%3A%24J%24537)*1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUltimately%2C%20what%20I%20need%20is%20a%20count%20of%20people%2Fpatients%20on%20the%20ward%20from%2001%20Jan%202019%20through%20to%20the%20end%20of%20April%20(I%20have%20these%20dates%20in%20Column%20L)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20and%20assistance%20is%20very%20much%20appreciated%20%3A-)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E%3CSTRONG%3EPerson%20%23%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EDate%20In%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EDate%20out%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E321%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E30%2F03%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E30%2F03%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E322%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E30%2F03%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E323%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E30%2F03%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E30%2F03%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E324%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E31%2F03%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E31%2F03%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E325%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E03%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E326%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E327%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E04%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E328%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E329%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E330%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E02%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E331%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E01%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E02%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E332%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E02%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E05%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E333%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E02%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E03%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E334%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E02%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E04%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E335%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E02%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E04%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E336%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E03%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E05%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E337%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E03%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E03%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E338%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E04%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E05%2F04%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-666653%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666864%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20people%20on%20a%20ward%20for%20a%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666864%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354176%22%20target%3D%22_blank%22%3E%40Ian_in_the_NHS%3C%2FA%3E%26nbsp%3B%2C%20if%20in%20A2%20is%2002%20Apr%2C%20when%20331%20and%20332%20do%20not%20meet%20your%20condition%20(date%20in%20J%20is%20more%20than%20A2).%20I%20guess%20it%20shall%20be%20%22more%20or%20equal%20to%22%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((A2%26gt%3B%3D%24I%245%3A%24I%24537)*(A2%26lt%3B%3D%24J%245%3A%24J%24537))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-671012%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20people%20on%20a%20ward%20for%20a%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-671012%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20that%2C%20it%20works!%26nbsp%3B%20It's%20amazing%20how%20much%20difference%20two%20little%20characters%20could%20make!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBrgds%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-672388%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20people%20on%20a%20ward%20for%20a%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-672388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354176%22%20target%3D%22_blank%22%3E%40Ian_in_the_NHS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Ian%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ian_in_the_NHS
New Contributor

Morning everyone

I'm using Office/Excel 2010 and have a long list of patients with a date they arrived on a ward and the date the left the ward; what i need is a count of all the people that are on the ward each day.  In the excerpt below, I can see that on the 2nd of April  there were 4 (325, 327, 330, 331) who were already on the ward and another 4 (332,333,334,335) who were admitted.

 

I've tried asking colleagues and Dr Google and had a go with the following formula which gives 9 for some reason:

=SUMPRODUCT((A2>$I$5:$I$537)*1,(A2<$J$5:$J$537)*1)

 

Ultimately, what I need is a count of people/patients on the ward from 01 Jan 2019 through to the end of April (I have these dates in Column L)

 

Any help and assistance is very much appreciated :-)

 

Ian

 

Person #

Date In

Date out

321

30/03/19

30/03/19

322

30/03/19

01/04/19

323

30/03/19

30/03/19

324

31/03/19

31/03/19

325

01/04/19

03/04/19

326

01/04/19

01/04/19

327

01/04/19

04/04/19

328

01/04/19

01/04/19

329

01/04/19

01/04/19

330

01/04/19

02/04/19

331

01/04/19

02/04/19

332

02/04/19

05/04/19

333

02/04/19

03/04/19

334

02/04/19

04/04/19

335

02/04/19

04/04/19

336

03/04/19

05/04/19

337

03/04/19

03/04/19

338

04/04/19

05/04/19

 

3 Replies
Solution

@Ian_in_the_NHS , if in A2 is 02 Apr, when 331 and 332 do not meet your condition (date in J is more than A2). I guess it shall be "more or equal to" like

=SUMPRODUCT((A2>=$I$5:$I$537)*(A2<=$J$5:$J$537))

 

Hi @Sergei Baklan 

 

Many thanks for that, it works!  It's amazing how much difference two little characters could make!

 

Brgds

Ian

 

@Ian_in_the_NHS 

Hi Ian, glad to help

Related Conversations