Finding cell references

[Gary Thomson]

Hi

I have posted something similar to this before, and got
near the answer, but not quite. Any ideas?

Sheet 1, called "Unavailability", contains the following:

A B C D E F G H
1 Unit Used Not Used >5 1-Feb 2-Feb 3-Feb 4-Feb
2 Maths £5 £10 £15 a
3English £6 £12 £18 a ab b
4 Music £7 £14 £21 abc b abcde
5 P.E. £10 £20 £30
6Geog £15 £30 £45
7History £4 £8 £12
8Drama £3 £6 £9
9Science £7.50 £15 £22.50 a
10 .
11 .
.. .
.. .

Column A lists the Units in a school.
Columns B,C and D give various amounts that are attributed
to Units based on a set of conditions (Used, Not Used and

5 days Unavailability)

The Range E2:H11 (which is much bigger but is simplified
here for presentation) gives the reference of faults that
have affected the corresponding Unit on the corresponding
day.


The second sheet, called "Council Usage", contains the
following:

A B C D E F G H
1 Unit - - - 1-Feb 2-Feb 3-Feb 4-Feb
2 Maths - - -
3 English - - - y y
4 Music - - - y
5 P.E. - - - y
6 Geog - - -
7 History - - -
8 Drama - - -
9 Science - - -
10 .
11 .
.. .
.. .

Where a "y" represents that the Unit has been Used on the
corresponding day.


The Third sheet, called "Consecutive Unavailability",
contains the following:

A B C D E F G H
1 Unit - - - 1-Feb 2-Feb 3-Feb 4-Feb
2 Maths - - - 1
3 English - - - 1 2 3
4 Music - - - 1 2 3
5 P.E. - - -
6 Geog - - -
7 History - - -
8 Drama - - -
9 Science - - - 1
10 .
11 .
.. .
.. .

The numbers in this sheet represent the number of
consecutive days that a Unit has been Unavailable



The Formula I want would do the following:

.. Find all occurrences of the letter in question in
the "Unavailability" sheet. (I.e. find all occurrences
of "a");
.. For each occurrence of "a", check the corresponding cell
in "Council Usage". If that cell contains a "y", then the
amount to be added is the amount in column B of
the "Unavailability" sheet (no matter what the value is in
the corresponding cell of the "Consecutive Unavailability"
sheet);
.. If the corresponding cell in "Council Usage" does not
contain a "y", then the amount to be added is the amount
in column C of the "Unavailability" Sheet (if the
corresponding cell in "Consecutive Unavailability" is less
or equal to 5), or the amount in Column D of
the "Unavailability" sheet (if the corresponding cell
in "Consecutive Unavailability" is greater than 5).

For example, Fault "a" occurs in the Maths Unit on 1-Feb
(cell E2 of the "Unavailability" Sheet, and the Unit is
not used (there is no "y" in cell E2 of the "Council
Usage" sheet) and the Unit has been consecutively
Unavailable for less than 5 days (cell E2 in
the "Consecutive Unavailability" sheet has a "1"),
therefore the deduction is the amount in column C of
the "Unavailability" Sheet (in fact, cell C2), which is
£10.

Fault "a" also occurs in cell E3 of the "Unavailability"
Sheet (English unit on 1-Feb), and that Unit is Used on
that day (there is a "y" in cell E3 of the "Council Usage"
sheet), therefore the deduction is the amount in column B
of the "Unavailability" Sheet (in fact, cell B3), which is
£6.

If this was continued for all occurrences of "a", the
total attributed to fault "a" would then be:

.. £10 (as above)
.. £6 (as above)
.. £12 (English Unavailable on 2-Feb, Unit not Used, Cons.
Unav = 2)
.. £7 (Music Unavailable on 1-Feb, Unit is Used)
.. £14 (Music Unavailable on 3-Feb, Unit not Used, Cons.
Unav = 3)
.. £15 (Science Unavailable on 1-Feb, Unit not Used, Cons.
Unav = 1)

which would give a total of £64 attributable to fault "a".

I need the formula to calculate the amount attributable to
fault "a", fault "b", fault "c", and so on.