[Array or Pivot?] Make a sum if the value of one cell falls within a date range.

Good morning all,

In the attached example there are 20 projects which have three stages each. The projects are worth different amounts and I will only get paid if the projects complete the three steps. You can see at the moment I will get paid for projects 1-8, 9 will be late, 10-12 get paid and the others will be too late.

I think I need an array formula combined with an "if" statement for the dates, or is it a pivot table I need?

Thanks in advance

Hi
what are you trying to achieve?

Hi, thanks for the quick response. I have updated the sheet to show money values.

I get paid if the projects complete the three steps by the end of 2012. In the attached example, the following projects complete all three steps before the end of 2012:


Project 1
Project 2
Project 3
Project 4
Project 5
Project 6
Project 7
Project 8

Project 10
Project 11
Project 12

The other projects overrun to 2013 and beyond for the third step, therefore I won't get paid. Currently, with the above projects completing before end 2012, I will earn £63.

At the moment I calculate that manually (column J). But I want it to know my change in earnings automatically if say cell G18's date was before 31/12/2012 (if G18 is before 31/12/2012 then I will earn £12 and my total will be £75).

I think it can be done using an array formula which I tried in cell I24. In English, the question reads like this:

"Sum the values in column D If the date in column G for that row is before 31/12/2012".

I just don't know how to say that in Excel-speak. Cheers!

Hi,

You could just use If statement to get the Values before "31/12/2012" data (i.e J3=IF(G3<$N$3,D3,""), where N3 = 31-Dec-2012). Confusing stuff here is in your example you consider G3 before 2013 but not G19 (as mention in I3 and I19, per your table)

Vinod

Thanks so much again, Vinod!

I have updated this in the attached, it works fine.