Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Need some help with a counting function

Hello,

I use a simple sheet to organize the timetable of my patients. Now I'd like to count how many different patients I attend, without counting a patient twice if I attend him twice a week for example. My sheet looks like this:



MonTueWedThu
JohnSteveJohnWesley
MaryHarryDeborah
PeterArnoldCarol
ChrisMarySarah
KarenKaren
CarolLarryPeter



So the answer would be 13, because I attend 13 different patients a week. Fields in blank should no be counted, the cell contend changes frequently.

A friend of mine tried to help me with a countif function but it didn't work out for me, it would count the fields left blank too.


Any idea?


Thanks, gabriel

Posted on Nov 22, 2013 9:28 AM

Reply
Question marked as Best reply

Posted on Nov 22, 2013 10:13 AM

Hi Gabriel,


Here's an approach that may work for you:

User uploaded file

I have used two auxiliary tables. One counts the number of visits for the patient in each time slot. The second auxiliary table calculates the total distinct patient names.


The formula in PatientFrequency is:


=COUNTIF(PatientCalendar::$A:$D, PatientCalendar::A2)


The formula in d]DistinctPatients is:


=COUNTIF(PatientFrequency::$A:$D, 1)+COUNTIF(PatientFrequency::$A:$D, 2)÷2


I think you can see what to do if there is a possiblilty of more than two visits per week by the same patient. For instance, if the maximum number of visits per week is 4, you would write:


=COUNTIF(PatientFrequency::$A:$D, 1)+COUNTIF(PatientFrequency::$A:$D, 2)÷2+COUNTIF(PatientFrequency::$A:$D, 3)÷3+COUNTIF(PatientFrequency::$A:$D, 4)÷4


Regards,

Jerry

24 replies
Question marked as Best reply

Nov 22, 2013 10:13 AM in response to gbalemao

Hi Gabriel,


Here's an approach that may work for you:

User uploaded file

I have used two auxiliary tables. One counts the number of visits for the patient in each time slot. The second auxiliary table calculates the total distinct patient names.


The formula in PatientFrequency is:


=COUNTIF(PatientCalendar::$A:$D, PatientCalendar::A2)


The formula in d]DistinctPatients is:


=COUNTIF(PatientFrequency::$A:$D, 1)+COUNTIF(PatientFrequency::$A:$D, 2)÷2


I think you can see what to do if there is a possiblilty of more than two visits per week by the same patient. For instance, if the maximum number of visits per week is 4, you would write:


=COUNTIF(PatientFrequency::$A:$D, 1)+COUNTIF(PatientFrequency::$A:$D, 2)÷2+COUNTIF(PatientFrequency::$A:$D, 3)÷3+COUNTIF(PatientFrequency::$A:$D, 4)÷4


Regards,

Jerry

Nov 22, 2013 1:43 PM in response to gbalemao

Hi Gabriel,


Here's another, more generalized, approach that doesn't need adjusting for maximum number of visits/week at the cost of just a few more formulas.🙂 But only two tables!


User uploaded file


Formula in A2 and copied down:

=ROUNDUP((ROW(cell)−1)÷$B$27,0)

User uploaded file


Formula in B2 and copied down:

=IF(MOD(ROW(cell)−1,$B$27)=0,$B$27,MOD(ROW(cell)−1,$B$27))

User uploaded file


The formula in C2 and copied down:

=OFFSET(Schedule::$A$1,0,B−1)

User uploaded file


The formula in D2 and copied down:

=IFERROR(OFFSET(Schedule::$A$1,A2,B2−1,rows,columns),"")

User uploaded file


The formula in E2 and copied down:

=IF(AND(D2≠0,LEN(D2)>0),COUNTIF($D$1:D2,D2),"")

User uploaded file

The formula in F2 and copied down:

=IFERROR(IF(E=1,1,""),"")

User uploaded file

The formula in F27: =SUM(F)


The value in B27 is 4, the number of day columns in the Schedule table. You would change that if you increase or decrease the number of days on which you see patients.


Column C is cosmetic, not needed in the calculation.


The error triangle means all of the cells in the Schedule table are already accounted for in the rows above.


Probably more compact ways to do this but it gets the job done.


SG

Nov 22, 2013 2:57 PM in response to Jerrold Green1

Hello Guys, thanks for your help. I got a lot o input now and need to see what works out best for me. In the meantime my friend wrote some formula for me. I copied the formula of the first line of every column so you can figure out maybe.. (I couldn't find the translation for BEREICHE.VERBINDEN, thats german and means sth like connect.area). We used a two table solution as well. Ah, and I asked him to add a feature so it wouldn't count the word "Grupo" which would mean group in portuguese. That's because I have some groups in the timetable as well.


IF(AND(COUNTIF(Tabelle 1::$B$2:B2;Tabelle 1::2ª '13:00 - 13:40')=0;Tabelle 1::2ª '13:00 - 13:40'≠"";COUNTIF(Tabelle 1::2ª '13:00 - 13:40';"*Grupo*")=0);1;"")


IF(AND(COUNTIF(BEREICHE.VERBINDEN(FALSE;Tabelle 1::$B$3:$B$9;Tabelle 1::$C$2:C2);Tabelle 1::3ª '13:00 - 13:40')=0;Tabelle 1::3ª '13:00 - 13:40'≠"";COUNTIF(Tabelle 1::3ª '13:00 - 13:40';"*Grupo*")=0 );1;"")


IF(AND(COUNTIF(BEREICHE.VERBINDEN(FALSE;Tabelle 1::$B$3:$B$9;Tabelle 1::$D$2:D2;Tabelle 1::$C$3:$C$9);Tabelle 1::4ª '13:00 - 13:40')=0;Tabelle 1::4ª '13:00 - 13:40'≠"";COUNTIF(Tabelle 1::4ª '13:00 - 13:40';"*Grupo*")=0 );1;"")


IF(AND(COUNTIF(BEREICHE.VERBINDEN(FALSE;Tabelle 1::$B$3:$B$9;Tabelle 1::$E$2:E2;Tabelle 1::$C$3:$C$9;Tabelle 1::$D$3:$D$9);Tabelle 1::5ª '13:00 - 13:40')=0;Tabelle 1::5ª '13:00 - 13:40'≠"";COUNTIF(Tabelle 1::5ª '13:00 - 13:40';"*Grupo*")=0 );1;"")


I'm doing this on my iPad, so I'm a bit limited to post screenshots here.

Nov 22, 2013 2:50 PM in response to Jerrold Green1

Hi Jerry,


Offering my "unclean" multi-expression solution wasn't meant in any way to denigrate yours. I figured out a lot of things while going through the exercise, and thought others might pick up some ideas from what I did. Obviously your approach is neat and quick, not quick and dirty.


Of course with yours it's tough to get a list of names of patients seen that week, should that be useful. With my admittedly verbose approach all you have to do is filter on F and you've got your list:


User uploaded file


SG

Nov 22, 2013 4:25 PM in response to gbalemao

Hi Gabriel,


Here's an alternate approach that uses an auxiliary table to do a progressive count of occurrences of each name in the Main table, and a Footer row cell on the Main table to count the 1s (marking the initial occurrence of each name) in the Aux table.

User uploaded file

Aux contains one formula for each column, entered in row 2, then filled down the rest of the column:


A2: =COUNTIF(Main :: $A$2:A2,Main :: A2)

B2: =COUNTIF(Main :: $A$2:$A$7,Main :: B2)+COUNTIF(Main :: $B$2:$B2,Main :: B2)

C2: =COUNTIF(Main :: $A$2:$B$7,Main :: C2)+COUNTIF(Main :: $C$2:C2,Main :: C2)

D2: =COUNTIF(Main :: $A$2:$C$7,Main :: D2)+COUNTIF(Main :: $D$2:$D2,Main :: D2)


As can be seen, each formula consists of the count of the name in all full columns to the left of the current one, plus the count of that name in the cells in the current column at or above the current row.


The result is a 1 in the cell corresponding to the first occurrence of the name in Main, a 2 in the cell corresponding to the second occurrence, etc.


Main contains a single formula in Cell B8. Row 8 is a Footer Row.


B8: =COUNTIF(Aux :: A2:D7,1)


Those progressive counts would include the name "Grupo", of course, and the total would include Grupo if it appeared in the Main table.


Grupo (or of any other specific 'name' that you want to eliminate from the total) can be handled with the addition of an IF statement to the formula in Main::B8 that counts the occurrences of that 'name' in Main, and subtracts 1 for the count of distinct names if Grupo appears one or more times in the table.


Min::B8: =COUNTIF(Aux :: A2:D7,1)-IF(COUNTIF(A2:D7,"Grupo")>0,1,0)

User uploaded file

Regards,

Barry

Nov 22, 2013 8:56 PM in response to Jerrold Green1

Jerry,


Your first link worked fine here. No formulas broken in the download. The new link is fine too, with the addition of Jerry and Marc on Tuesday. But in both versions I think you forgot to include three-timer Peter: a count of 16 should be 17 in the first version and your current count should be 19 instead of 18. That's the hazard of such a nice, compact presentation; it's hard to double-check what's under the hood.


Here's mine, updated to your latest Schedule table, also excluding Grupo (via formula in F), and with the filter on column F turned on to hide blank cells:


User uploaded file


A2 and down =ROUNDUP((ROW(cell)−1)÷$B$39,0)

B2 and down =IF(MOD(ROW(cell)−1,$B$39)=0,$B$39,MOD(ROW(cell)−1,$B$39))

C2 and down =OFFSET(Schedule::$A$1,0,B−1) [for ease of auditing only]

D2 and down =IFERROR(OFFSET(Schedule::$A$1,A2,B2−1,rows,columns),"")

E2 and down =IF(AND(D2≠0,LEN(D2)>0),COUNTIF($D$1:D2,D2),"")

F2 and down =IFERROR(IF(D="Groupo","",IF(E=1,1,"")),"")

F39 =SUM(F)



SG

Need some help with a counting function

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.