Counting instances in string

[John in Wembley]

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

 

[Dave O]

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

Dave O

 

[John in Wembley]

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

 

[John in Wembley]

Is sumproduct the way to go?

 

[Dave O]

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

 

[PCLIVE]

If it i

 

[PCLIVE]

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

 

[Dave O]

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

 

[PCLIVE]

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

--

 

[Dave O]

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.

 

[John in Wembley]

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)))

amazing work thanks Chaps
cheers from wembley UK

 

[David Biddulph]

In what way are the columns grouped "inside the double unary"? Doesn't the
second double unary minus apply only to the bracketed term which it
precedes, namely the column B term?
The first double unary minus is applied to the column A term, but is perhaps
questionable whether you need the second double unary minus in front of the
column B term, bearing in mind that you are adding it to the column C term
and the column D term and the column E term. It may be worth trying
without, in the hope that the arithmetic operation you are conducting will
do the job of coercing the logical to a number anyway.