Counting defined values
=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!
|