I am trying to count the number of times either "12 12" or "8 16" (these
being shift patterns) occur in a column. My table is set out in a calendar
format, with the value beside a persons name. I need to count how many times
there was 2 or less of the above values in one day, so I am assuming i should
count each column, then have a SUM at the end of it. My aim is to count how
many times in a year, there were days with 2 or less of the above values. ie
0, 1 or 2 people working that day. I have already set conditional formatting
so that these values are turned up in red, so I dont know if that helps,
(although other values in the table are red also!)

I am sorry if this is confusing, I am a bit of a novice user, and am VERY
confused! Ive tried to explain it a bit below.

Eg
A B C D E F G
1 2 3 4 5 6 7

Mr A 12 12 8 16 N/A N/A 8 16 N/A N/A
Mr B N/A 12 12 N/A 8 16 N/A 12 12 N/A
Mr C N/A 8 16 N/A 12 12 N/A 8 16 N/A

Therefore, column A is one, column B is 3, C is 0, D is 2 etc

I only want a total if there are 2 or less, so the above would be 6.

Any help would be appreciated!

Hi,

I think I understand which probably means I don't! However as a 2 stage
proces try this:-

Put the formula in a cell (I1 in this example)
=COUNTIF(B1:B20,"8 16")+COUNTIF(B1:B20,"12 12")
Drag the formula right to cover all 7 columns (to O1 in this example
In another cell type the formula
=COUNTIF(I1:O1,"<=2")

With your given data this gives an answer of 6

Mike

"franki_85" wrote:

I am trying to count the number of times either "12 12" or "8 16" (these
being shift patterns) occur in a column. My table is set out in a calendar
format, with the value beside a persons name. I need to count how many times
there was 2 or less of the above values in one day, so I am assuming i should
count each column, then have a SUM at the end of it. My aim is to count how
many times in a year, there were days with 2 or less of the above values. ie
0, 1 or 2 people working that day. I have already set conditional formatting
so that these values are turned up in red, so I dont know if that helps,
(although other values in the table are red also!)

I am sorry if this is confusing, I am a bit of a novice user, and am VERY
confused! Ive tried to explain it a bit below.

Eg
A B C D E F G
1 2 3 4 5 6 7

Mr A 12 12 8 16 N/A N/A 8 16 N/A N/A
Mr B N/A 12 12 N/A 8 16 N/A 12 12 N/A
Mr C N/A 8 16 N/A 12 12 N/A 8 16 N/A

Therefore, column A is one, column B is 3, C is 0, D is 2 etc

I only want a total if there are 2 or less, so the above would be 6.

Any help would be appreciated!

=SUMPRODUCT(COUNTIF(B:B,{"12 12","8 16"}))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"franki_85" wrote in message
...
I am trying to count the number of times either "12 12" or "8 16" (these
being shift patterns) occur in a column. My table is set out in a
calendar
format, with the value beside a persons name. I need to count how many
times
there was 2 or less of the above values in one day, so I am assuming i
should
count each column, then have a SUM at the end of it. My aim is to count
how
many times in a year, there were days with 2 or less of the above values.
ie
0, 1 or 2 people working that day. I have already set conditional
formatting
so that these values are turned up in red, so I dont know if that helps,
(although other values in the table are red also!)

I am sorry if this is confusing, I am a bit of a novice user, and am VERY
confused! Ive tried to explain it a bit below.

Eg
A B C D E F G
1 2 3 4 5 6 7

Mr A 12 12 8 16 N/A N/A 8 16 N/A N/A
Mr B N/A 12 12 N/A 8 16 N/A 12 12 N/A
Mr C N/A 8 16 N/A 12 12 N/A 8 16 N/A

Therefore, column A is one, column B is 3, C is 0, D is 2 etc

I only want a total if there are 2 or less, so the above would be 6.

Any help would be appreciated!

I will ignore your A, B, C etc since it does not fit with Excel's column
headers
I have your 1,2,3... in B1:H1
In A2:A4 I have your names
In B2:H4 I have your data
In B5 (under the last entry of the first column of data) I have the formula
=COUNTIF(B2:B4,"12 12")+COUNTIF(B2:B4,"8 16")
This is copied across to H5 giving the values: 1,3,0,2,1,2,0
In I5 I used =COUNTIF(B6:H6,"<3") since you want to know how may times there
are values of 2 or less (ie values less than 3 since we are working with
whole numbers). This returns the value 6
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"franki_85" wrote in message
...
I am trying to count the number of times either "12 12" or "8 16" (these
being shift patterns) occur in a column. My table is set out in a
calendar
format, with the value beside a persons name. I need to count how many
times
there was 2 or less of the above values in one day, so I am assuming i
should
count each column, then have a SUM at the end of it. My aim is to count
how
many times in a year, there were days with 2 or less of the above values.
ie
0, 1 or 2 people working that day. I have already set conditional
formatting
so that these values are turned up in red, so I dont know if that helps,
(although other values in the table are red also!)

I am sorry if this is confusing, I am a bit of a novice user, and am VERY
confused! Ive tried to explain it a bit below.

Eg
A B C D E F G
1 2 3 4 5 6 7

Mr A 12 12 8 16 N/A N/A 8 16 N/A N/A
Mr B N/A 12 12 N/A 8 16 N/A 12 12 N/A
Mr C N/A 8 16 N/A 12 12 N/A 8 16 N/A

Therefore, column A is one, column B is 3, C is 0, D is 2 etc

I only want a total if there are 2 or less, so the above would be 6.

Any help would be appreciated!

Thank you all.....the formula worked well! It is greatly appreciated!