G
Gary Thomson
I have 3 sets of data on the same sheet:
All 3 sets have the same first 4 columns (i.e. Unit, 50%,
100% and 130%).
The first range of data is A1
10, which I shall
call "DATES":
UNAVAILABLE
A B C D E F G...
1 Unit 50% 100% 130% 1-Feb 2-Feb 3-Feb
2 Maths £35 £70 £91 x x x
3 English £30 £60 £78 x x
4 Music £25 £50 £65 x
5 Science £20 £40 £52
6 P.E. £15 £30 £39
.. .
.. .
.. .
The second range is in A2130, which I shall call "USAGE":
USAGE
A B C D E F G
21 Unit 50% 100% 130% 1-Feb 2-Feb 3-Feb
22 Maths £35 £70 £91 y
23 English £30 £60 £78 y
24 Music £25 £50 £65
25 Science £20 £40 £52 y
26 P.E. £15 £30 £39
.. .
.. .
.. .
The third range is in A4150, which I shall
call "CONSECUTIVE DAYS GREATER THAN 5?":
CONSECUTIVE DAYS GREATER THAN 5?
A B C D E F G
41 Unit 50% 100% 130% 1-Feb 2-Feb 3-Feb
42 Maths £35 £70 £91 1 2 3
43 English £30 £60 £78 1 2
44 Music £25 £50 £65 5
45 Science £20 £40 £52
46 P.E. £15 £30 £39
.. .
.. .
.. .
I want a formula that will sum the amounts attributed to
each date based on the combination of "x"'s (in
the "UNAVAILABLE" range), "y"'s (in the "USAGE" range),
and number of days Unavailable (in the CONSECUTIVE DAYS
GREATER THAN 5?" table).
FOR each cell from E2 to E10 in the "UNAVAILABLE" range
IF the required cell contains an "x":
THEN IF the corresponding cell in the "USAGE" range
contains a "y" then add on the value in the "B" column, but
IF the corresponding cell in the "USAGE" range does NOT
contain a "y":
THEN IF the corresponding cell in the "CONSECUTIVE DAYS
GREATER THAN 5?" range is >5, add on the value in the "D"
column, otherwise add on the value in the "C" column;
IF in the "UNAVAILABLE" range, the required cell does not
contain an "x":
THEN IF the corresponding cell in the USAGE range contains
a "y", then add on the value in the "B" column, otherwise
add on the value in the "C" column.
Can this be done using some sort of SUMIF or SUMPRODUCT
formula?
I will be interested to see what you can make of this.
If I have not explained myself well, just let me know and
I'll try to make it clearer.
Thanx
All 3 sets have the same first 4 columns (i.e. Unit, 50%,
100% and 130%).
The first range of data is A1
10, which I shall call "DATES":
UNAVAILABLE
A B C D E F G...
1 Unit 50% 100% 130% 1-Feb 2-Feb 3-Feb
2 Maths £35 £70 £91 x x x
3 English £30 £60 £78 x x
4 Music £25 £50 £65 x
5 Science £20 £40 £52
6 P.E. £15 £30 £39
.. .
.. .
.. .
The second range is in A21
30, which I shall call "USAGE":USAGE
A B C D E F G
21 Unit 50% 100% 130% 1-Feb 2-Feb 3-Feb
22 Maths £35 £70 £91 y
23 English £30 £60 £78 y
24 Music £25 £50 £65
25 Science £20 £40 £52 y
26 P.E. £15 £30 £39
.. .
.. .
.. .
The third range is in A41
50, which I shall call "CONSECUTIVE DAYS GREATER THAN 5?":
CONSECUTIVE DAYS GREATER THAN 5?
A B C D E F G
41 Unit 50% 100% 130% 1-Feb 2-Feb 3-Feb
42 Maths £35 £70 £91 1 2 3
43 English £30 £60 £78 1 2
44 Music £25 £50 £65 5
45 Science £20 £40 £52
46 P.E. £15 £30 £39
.. .
.. .
.. .
I want a formula that will sum the amounts attributed to
each date based on the combination of "x"'s (in
the "UNAVAILABLE" range), "y"'s (in the "USAGE" range),
and number of days Unavailable (in the CONSECUTIVE DAYS
GREATER THAN 5?" table).
FOR each cell from E2 to E10 in the "UNAVAILABLE" range
IF the required cell contains an "x":
THEN IF the corresponding cell in the "USAGE" range
contains a "y" then add on the value in the "B" column, but
IF the corresponding cell in the "USAGE" range does NOT
contain a "y":
THEN IF the corresponding cell in the "CONSECUTIVE DAYS
GREATER THAN 5?" range is >5, add on the value in the "D"
column, otherwise add on the value in the "C" column;
IF in the "UNAVAILABLE" range, the required cell does not
contain an "x":
THEN IF the corresponding cell in the USAGE range contains
a "y", then add on the value in the "B" column, otherwise
add on the value in the "C" column.
Can this be done using some sort of SUMIF or SUMPRODUCT
formula?
I will be interested to see what you can make of this.
If I have not explained myself well, just let me know and
I'll try to make it clearer.
Thanx