Multi Formula With Exclusions For Jail Inmate Count

[Dave]

Problem:
In Cells V9:V28, I have a time listed formatted as [hh]mm. The time in these
cells represent the amount of time an inmate was held in custody.

In Cell G31, I have the total number of Male Adults in custody shown. In
Cell G32, I have the total number of Female Adults in custody shown. In Cell
G36, I have the total number of Juvenile Males in custody shown and in G37, I
have the total number of Juvenile Females in custody shown.

Cells F9:F28 is where the “Jâ€â€™s are listed, when applicable, and Cells
G9:G28 just reflects either “M for Male or “F†for Female regardless in adult
or juvenile. What I want to accomplish using the above, if possible, is the
following:

First Formula:
In Cell L31, the amount of time spent in custody for Male Adults only. In
Cell L32, the amount of time spent in custody for Female Adults only. In Cell
L34, the amount of time spent in custody for Male Juveniles only. In Cell
L35, the amount of time spent in custody for Female Juveniles only.

Second Formula:
In Cell L40, I want to show the average stay for all Juveniles (male &
female) and in Cell L42, I want to show the average stay for all Adults (male
and female).

I’m guessing the times shown in Cells V9:V28 must be divided by the number
on inmates shown in G31 for Male Adults, G32 for Female Adults, F9:F28 and
G36 for Male Juveniles and F9:F28 and G37 for Female Juveniles. I’m not sure
how to exclude each of the three other groups from the count.

I sincerely hope the above makes sense. I have tried to be as complete and
comprehensive in my explanation as possible. Any assistance would be greatly
appreciated. There are many other formulas used in this spread sheet. I do
not have any objections to emailing a copy of my worksheet to anyone if it
would assist in helping resolving the issue.

Thank you.

 

[Bob Phillips]

Problem:
In Cells V9:V28, I have a time listed formatted as [hh]mm. The time in these
cells represent the amount of time an inmate was held in custody.

In Cell G31, I have the total number of Male Adults in custody shown. In
Cell G32, I have the total number of Female Adults in custody shown. In Cell
G36, I have the total number of Juvenile Males in custody shown and in G37, I
have the total number of Juvenile Females in custody shown.

Cells F9:F28 is where the "J"'s are listed, when applicable, and Cells
G9:G28 just reflects either "M for Male or "F" for Female regardless in adult
or juvenile. What I want to accomplish using the above, if possible, is the
following:

First Formula:
In Cell L31, the amount of time spent in custody for Male Adults only. In
Cell L32, the amount of time spent in custody for Female Adults only. In Cell
L34, the amount of time spent in custody for Male Juveniles only. In Cell
L35, the amount of time spent in custody for Female Juveniles only.

L31: =SUMPRODUCT(--($F$9:$F$28=""),--($G$9:$G$28="M"),$V$9:$V$28)
L32: =SUMPRODUCT(--($F$9:$F$28=""),--($G$9:$G$28="F"),$V$9:$V$28)
L33: =SUMPRODUCT(--($F$9:$F$28="J"),--($G$9:$G$28="M"),$V$9:$V$28)
L34: =SUMPRODUCT(--($F$9:$F$28="J"),--($G$9:$G$28="F"),$V$9:$V$28)

Second Formula:
In Cell L40, I want to show the average stay for all Juveniles (male &
female) and in Cell L42, I want to show the average stay for all Adults (male
and female).

L40: =AVERAGE(IF($F$9:$F$28="J",$V$9:$V$28))
L42: =AVERAGE(IF($F$9:$F$28="",$V$9:$V$28))

Both of thes last two formulae are array formulae, and should be committed
with Ctrl-Shift-Enter, not just Enter.

..

 

[Ron Rosenfeld]

Problem:
In Cells V9:V28, I have a time listed formatted as [hh]mm. The time in these
cells represent the amount of time an inmate was held in custody.

In Cell G31, I have the total number of Male Adults in custody shown. In
Cell G32, I have the total number of Female Adults in custody shown. In Cell
G36, I have the total number of Juvenile Males in custody shown and in G37, I
have the total number of Juvenile Females in custody shown.

Cells F9:F28 is where the “J”’s are listed, when applicable, and Cells
G9:G28 just reflects either “M for Male or “F” for Female regardless in adult
or juvenile. What I want to accomplish using the above, if possible, is the
following:

First Formula:
In Cell L31, the amount of time spent in custody for Male Adults only. In
Cell L32, the amount of time spent in custody for Female Adults only. In Cell
L34, the amount of time spent in custody for Male Juveniles only. In Cell
L35, the amount of time spent in custody for Female Juveniles only.

Second Formula:
In Cell L40, I want to show the average stay for all Juveniles (male &
female) and in Cell L42, I want to show the average stay for all Adults (male
and female).

I’m guessing the times shown in Cells V9:V28 must be divided by the number
on inmates shown in G31 for Male Adults, G32 for Female Adults, F9:F28 and
G36 for Male Juveniles and F9:F28 and G37 for Female Juveniles. I’m not sure
how to exclude each of the three other groups from the count.

I sincerely hope the above makes sense. I have tried to be as complete and
comprehensive in my explanation as possible. Any assistance would be greatly
appreciated. There are many other formulas used in this spread sheet. I do
not have any objections to emailing a copy of my worksheet to anyone if it
would assist in helping resolving the issue.

Thank you.

If I understand you correctly, you could use the following formulas:

G31: =SUMPRODUCT((F9:F28<>"J")*(G9:G28="M"))
G32: =SUMPRODUCT((F9:F28<>"J")*(G9:G28="F"))

G36: =SUMPRODUCT((F9:F28="J")*(G9:G28="M"))
G37: =SUMPRODUCT((F9:F28="J")*(G9:G28="F"))


L31: =SUMPRODUCT(($F$9:$F$28<>"J")*($G$9:$G$28="M")*($V$9:$V$28))
L32: =SUMPRODUCT(($F$9:$F$28<>"J")*($G$9:$G$28="F")*($V$9:$V$28))

L36: =SUMPRODUCT(($F$9:$F$28="J")*($G$9:$G$28="M")*($V$9:$V$28))
L37: =SUMPRODUCT(($F$9:$F$28="J")*($G$9:$G$28="F")*($V$9:$V$28))

L40: =(L36+L37)/(G36+G37)

L42: =(L31+L32)/(G31+G32)

--ron