occurences calculation HEeeelllpp?

[EARTHWALKER]

Hiya peeps. Ok this is part of what my spreadsheet does.

It calculates how many times someone is off sick which is simple wit
the countif statement looking for the letter "S".

Now, what I'm now trying to do is calculate over a month, how man
occurences of sick they had.

e.g I have say fred in my list and this is his last 10 days attendanc
record.

So stating at C1 (A1, B1 have other stuff in them like name and rank)

sick |present |present|holiday|sick|sick|present|present|sick|sick

So here, he has had 5 days sick. But only 3 occurences (more than
sick day in a row is classed as the same occurence) in the last 1
days.

Over a month and 600 records it would be a nightmare to do this all b
hand.
Is there a simple or even non simple way of doing this?
I've been trying for 2 days now with no success. I'm using excel 200
sp-2 with XP.

Any help would be greatly appreciated

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((C1:AA1="sick")*(D1:AB1<>"sick"))

assuming that your rows with entries ranges from C to AA. This formula
looks only for 'sick' entries which are not followed by another 'sick'
entry. Therefore the second part of the SUMPRODUCT formula is shifted
by one column

Frank

 

[EARTHWALKER]

Thank you for giving it a go so quickly.

I tried that and it came up with the answer #N/A. Well it's closer to
the actual answer of 3 that my many formulas came up with

 

[Frank Kabel]

Hi

one important think is, that both arrays in the SUMPRODUCT formula have
to be of the same size. In my example
C1:AA1
and
D1:AB1
are of the same size. If you - for example - would change the second
part to D1:AA1 you will get the "'N/A" error.

HTH.
Frank

 

[EARTHWALKER]

That's absolutely fantastic Frank, thank you very much m8. If I can
ever do anything for you in return...








____________________________________________________
Thank heavens for clever people

 

[Frank Kabel]

Thanks for the thanks
Frank