Hi team

I have a list of dates patients attended a group stored as rows.

Patient Name Session 1 Session2 Session3 Ses 4
John H 28 Sep 29 Sep 30 Sep 01 Oct

the business office wants a report of how many times they attended -
say in Sep.
is there some function to do this?
I just was a field at the start of the row counting the occourances of
(say) Sep.

Ive tinkerd but with no luck

cheers
John

Hi, John-
Are the dates stored as Excel-compatible dates, or as text strings?

Dave O

On Tue, 18 Sep 2007 09:35:24 -0700, Dave O <(E-Mail Removed)> wrote:

>Hi, John-
>Are the dates stored as Excel-compatible dates, or as text strings?

They are stored as dates which I guess is the challanges....
Do I need to go back and input these dates as text?

thanks john

Is sumproduct the way to go?

No, it works better as dates. I mocked up a grid based on your
example, with the headers "Patient Name, Session 1, Session 2, Session
3, Session 4" in cells A1:E1 and the names "Vera, Chuck, Dave" in
cells A2:A4. Cells B2:E4 have Excel compatible dates. Then I created a
summary grid with the headers "Patient Name" and "Sep-07" in A6:B6. B6
is entered as a Excel compatible date and formatted to display as
Sep-07. Patient Names, Vera Chuck and Dave, are entered in A7:A9.

In B7 I have the formula
=SUM(IF(A7=$A$2:$A$4,IF(MONTH($B$2:$E$4)=9,1,0)))

You can copy and paste the formula from this message into your
spreadsheet, but to get a proper return you must press
CTRL-Shift-Enter. This formula is an array function; the curly
brackets around the formula only appear when you simultaneously press
the CTRL-Shift-Enter.

Dave O

If it i

--

"John in Wembley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi team
>
> I have a list of dates patients attended a group stored as rows.
>
> Patient Name Session 1 Session2 Session3 Ses 4
> John H 28 Sep 29 Sep 30 Sep 01 Oct
>
> the business office wants a report of how many times they attended -
> say in Sep.
> is there some function to do this?
> I just was a field at the start of the row counting the occourances of
> (say) Sep.
>
> Ive tinkerd but with no luck
>
> cheers
> John

If it is only based on 4 sessions, then maybe:

=SUMPRODUCT(--(A1:A20=J1),--(MONTH(B1:B20)=K1)+(MONTH(C1:C20)=K1)+(MONTH(D120)=K1))

J1 contains the Name you want to search.
K1 contains the month "Number". (September = 9)

Change references as needed.

HTH,
Paul


--

"John in Wembley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi team
>
> I have a list of dates patients attended a group stored as rows.
>
> Patient Name Session 1 Session2 Session3 Ses 4
> John H 28 Sep 29 Sep 30 Sep 01 Oct
>
> the business office wants a report of how many times they attended -
> say in Sep.
> is there some function to do this?
> I just was a field at the start of the row counting the occourances of
> (say) Sep.
>
> Ive tinkerd but with no luck
>
> cheers
> John

Sorry, John- this is a better formula:
=SUM(IF(A7=$A$2:$A$4,IF(MONTH($B$2:$E$4)=MONTH($B$6),1,0)))

This is still an array formula but uses the date in the header cell B6
as an argument. That way you can quickly change the month you want to
count.

Our threads are crossing! I thought about Sumproduct too, but you'd
need to add Sumproduct for Session 1 plus Sumproduct for Session 2
plus Sumproduct for Session 3 plus Sumproduct for Session 4, and I
thought it was a bit much. If the data set is very large you may want
to consider this, though, because array formulas create a lot of
overhead within the file, increasing file size and increasing the
calculation time. In smaller data sets they work well.

Dave O

Oops. I missed session 4.

=SUMPRODUCT(--(A1:A20=J1),--(MONTH(B1:B20)=K1)+(MONTH(C1:C20)=K1)+(MONTH(D120)=K1)+(MONTH(E1:E20)=K1))

Regards,
Paul

--

"PCLIVE" <pclive(RemoveThis)@cox.net> wrote in message
news:%23sBecTh%(E-Mail Removed)...
> If it is only based on 4 sessions, then maybe:
>
> =SUMPRODUCT(--(A1:A20=J1),--(MONTH(B1:B20)=K1)+(MONTH(C1:C20)=K1)+(MONTH(D120)=K1))
>
> J1 contains the Name you want to search.
> K1 contains the month "Number". (September = 9)
>
> Change references as needed.
>
> HTH,
> Paul
>
>
> --
>
> "John in Wembley" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> Hi team
>>
>> I have a list of dates patients attended a group stored as rows.
>>
>> Patient Name Session 1 Session2 Session3 Ses 4
>> John H 28 Sep 29 Sep 30 Sep 01 Oct
>>
>> the business office wants a report of how many times they attended -
>> say in Sep.
>> is there some function to do this?
>> I just was a field at the start of the row counting the occourances of
>> (say) Sep.
>>
>> Ive tinkerd but with no luck
>>
>> cheers
>> John
>
>

Paul- I'd never seen SUMPRODUCT in the format that groups the columns
inside the double unary. Cool! I need to read up on that.