VLOOKUP & COUNTING AND SUMIFS in a DataSet

Hi All,

I have attached an example.

I need to automatically count the number of items (tenants) based on when their termination date is (referred to as term date -cell E22). I then want it to sum the "NRSF" & "Annual Rent" columns in the Dataset based on the termination date.

I know this is all confusing but it is more clear in the attached example.

In the attached example, the answers will go in cells C6:E17 (the cells that are highlighted yellow.) In the Cells G6:J17 I showed the answers that I want to appear in cells C6:17 when the process is automated.

The data set is ever changing so I will need flexible equations.

Please if you have any questions let me know. And Thank you very much for your help.

Hi
Countif and Sumproduct Year

See the file!

Regard

Hi again

Find better formula than the one I did!!

C7

Formula:

Please Login or Register  to view this content.

Copy down

D7

Formula:

Please Login or Register  to view this content.

Copy down

E7

Formula:

Please Login or Register  to view this content.

Copy down

Regard