Counting Blocks of data in a row / record

[msommerf]

Hi does anyone know if it is possible to count blocks of data in a row / record?

I have an absence spreadsheet which shows absence codes for employees when they have time off work. I am trying to find a formula which will count these blocks of codes and give me the total number of absences per month.(not total number of days)

Please see attached spreadsheet.

Any help appreciated.

[Andrew-R]

If you add this UDF to your workbook...

Please Login or Register  to view this content.

The you can put the formula:

Please Login or Register  to view this content.

In cell AH12 and it will count the absences.

[Marcol]

Try this

In AG12
=COUNTA($B12:$AF12)

In AH12
=COUNTIF($B12:$AF12,"L")

Hope this helps

[msommerf]

Thanks for the response Andrew-R.

I am unsure as to how I add a UDF to my worksheet.
Could you please explain?

Many thanks

[msommerf]

Marcol,

This only counts the absences with the letter "L" In need to count all of the blocks of data.

[DonkeyOte]

Perhaps:

Please Login or Register  to view this content.

[msommerf]

SUMPRODUCT does the Trick.

Thank You so much DONKEYOTE

[Andrew-R]

I *really* must learn how SUMPRODUCT works.

[DonkeyOte]

The link in my sig. is IMO the best tutorial I've come across - c/o Bob Phillips (member here, MS Excel MVP etc...)

I wrote an add-in a while back to generate the type of calculation tables Bob's page uses for error trapping / educational purposes.
With copious help from romperstomper & Richard Schollar the "seemingly" complete version is available at my box.net site: http://www.box.net/shared/tfpqhbeoes

Bob was doing some work at some stage I think trying to pull the original source values into the calc tables but that's a non-trivial task given complexity and variation of formulae - I've not heard anything on it since.
(this was put together a good while back)

[Marcol]

And I *really* must learn how to waken up!.

[msommerf]

Awake or Asleep, its good to have the help of all you guys.

Many thanks for solving my problem so quickly.

It is much appreciated

Thank you all

Mark.