I am attempting to create a SUMPRODUCT based formula that will allow me to
apply an array on a conditional basis. Specifically the problem is that there
is a table of data showing the following:
COL A COL B COL C COL D
COL E
Group Contract Date Value
Uplift post
end required pre contract
contract
date until end
end
Red a x 1
10%
Yellow b y 2
20%
Blue c z 3
30%
This is a simplification as there is actually 4,000 rows with 24 different
groupings etc. A calculation sheet then attempts to summarise the values over
different periods (4 week periods) of time for each grouping.
Assuming that there are no partial periods, I could use SUMPRODUCT((COL A =
specific group)*(COL D)*(COL E < specific date period)) to achieve the result
needed excluding the requirement to apply the percentage uplift to those
periods post contract end.
I can't see how to do this. Any ideas would be gratefully received?
|
Similar Threads
