Formula to count records by week and session

[rireland]

I have a lot of records that look like the list below. Its the data from entries to our film club. At the end of the year there will be 40,000 records. The list gets added to every week. I need to know the number of valid entries for each week and day. The week is the penultimate field (51 in this case) and the day is the last record. I also want to ignore the MULTI and INVALID. So the answers in this example would be:
51 1 =5
51 2 =3
51 3 =3

I have been doing the hard way by sort by "OK" and then using a basic COUNTIF formula for each week but I'd like a formula that I can apply the the whole file. So in mid year we would be up to week 26 and I'd like to just apply a formula to those 20,000 records .

Richard








844 191216 OK 311767812 6271 Deborah Gray S 51 1
844 191216 OK 1646107652 4681 Julie Wilson S 51 1
844 191216 OK 311764996 2140 Paul Haigh S 51 1
844 191216 OK 311763204 1369 Richard Neal S 51 1
844 191216 OK 4049245444 7005 Preprinted card5 S 51 1
839 201216 OK 311769092 6266 Barbara Ruttelle S 51 2
839 201216 OK 311768580 6308 Katrina Steriovich S 51 2
839 201216 MULTI 311763204 1369 Richard Neal S 51 2
839 201216 INVALID 1653583620 S 51 2
839 201216 MULTI 4049245444 7005 Preprinted card5 S 51 2
839 201216 OK 4055260420 2093 Leley Breeze S 51 2
829 211216 MULTI 311763204 1369 Richard Neal S 51 3
830 211216 MULTI 311764996 2140 Paul Haigh S 51 3
830 211216 OK 4050878724 7001 Preprinted card1 S 51 3
830 211216 OK 311697668 4753 Kathryn Cuthbertson S 51 3
830 211216 OK 311766532 919 Jaqui Stacey S 51 3

 

[Tetra201]

Try this:

=SUMPRODUCT(--(MID(TRIM($A$1:$A$50000),11,4)=" OK "),--(--(0&TRIM(LEFT(RIGHT(TRIM($A$1:$A$50000),4),2)))=51),--(--(0&RIGHT(TRIM($A$1:$A$50000)))=1))

 

[PaddyD]

Another approach, for what it's worth - do it 'long hand'.

1) Set up an extra column to flag for OK / MULTI / INVALID
2) Set up a column for weeks
3) Set up a column for days

...then use 'easier to manage' sumifs() / pivot tables off the new data...

 

[WarfritLive]

Try this:

=SUMPRODUCT(--(MID(TRIM($A$1:$A$50000),11,4)=" OK "),--(--(0&TRIM(LEFT(RIGHT(TRIM($A$1:$A$50000),4),2)))=51),--(--(0&RIGHT(TRIM($A$1:$A$50000)))=1))

Sorry to doubt, but somehow I doubt all his records are in column A. Probably just pasted funny

@OP try:
=SUMPRODUCT(--(C:C="OK"),--(J:J=1))
=SUMPRODUCT(--(C:C="OK"),--(J:J=2))
etc

 

[rireland]

Sorry, confused everyone with the way it has presented the data, its in a csv file initially and can easily be put into an excel file in columns.
Richard

 

[Tetra201]

Sorry to doubt, but somehow I doubt all his records are in column A. Probably just pasted funny...

A very good point, WarfritLive. Thank you.

In this case, COUNTIFS would work and would be faster:

=COUNTIFS(C:C,"OK",I:I,51,J:J,1)

 

[WarfritLive]

A very good point, WarfritLive. Thank you.

In this case, COUNTIFS would work and would be faster:

=COUNTIFS(C:C,"OK",I:I,51,J:J,1)

tru

 

[rireland]

Thanks - but I would need to run this formula 160 times - we operate the club 4 days a week, 40 weeks a year. What I ideally want is an output that looks like this:

5 1 200
5 2 343
.
51 3 121
51 4 0

That is, number of OK by week and day. At the beginning of the year, there would be 0 by most and as the year progressed and I put in more data, the answers would fill up. So the example above would be after day 3 of week 51. I would have an increasing file that maybe has 1000 rows after week 1 but by the end of the year has 40,000 rows. Hope that makes sense.

Richard