# Make a formula that reads from "date range" cells?

#### Marq

##### Well-known Member
I am using excel 2007

In my spreadsheet, I have a cell, Y20, that has a formula in that populates with the name of a project based on a date range. The date ranges are plugged into cells R1 & R2 (for example I will put 1-2-13 in R1 and 1-6-13 in R2....and based on the criteria in the formula in Y20 a project name will show up showing I wokred that job within that range....the job will have multiple entries within that range but I just need to see it listed one time...I just need to see we worked it withing that range...not how many times its listed in that range.)

What I am needing to see now, in cell AA20, and this is extensive, is this:

A formula in cell AA20 a formula with the following criteria: Based on the date ranges in R1 & R2.....and the job showing up in Y20....look within column L3:L3000 for all the listings of that job name...then look in column E for all of the 51015 listings...then looking in column F for all the 101...then in column O look for all the for "yes"'s.......once all of that criteria is met then sum up the hours in column K that meet all the criteria listed (that is withing the date ranges in R1 and R2).

Seems like it would be a huge sumproduct formula and I have written sumproducts before but the date range and the Y20 cell are confusing me on how to write that into a sumproduct.

here is an example of the information the formula would apply to, with the results:

Here is the info:

Column D Column E Column F Column K Column L Column O
1/3/13 51014 201 65.25 E-1234 No
1/3/13 51014 101 25 E-1234 No
1/3/13 51014 101 10 Project 3 of 3 No
1/3/13 51014 101 20 E-ABC No
1/2/13 51019 201 41 E-1234 Yes
1/2/13 51019 101 34.5 E-1234 Yes
1/2/13 51019 101 36.25 P00123 Yes
1/2/13 51019 101 50 Project E Yes
1/3/13 51019 101 26 E-ABC Yes
1/3/13 51019 201 31 E-1234 Yes
1/3/13 51019 101 31 E-1234 Yes
1/3/13 51019 700 40 Project 3 of 3 Yes
1/3/13 51019 101 29.75 Project 3 of 3 Yes
1/3/13 51019 700 95.5 Project 3 of 3 Yes
1/2/13 51014 101 10 P00123 No
1/4/13 51010 101 30 Project E No
1/4/13 51015 101 31 PV-ABC Yes

here are what the results would be:

COLUMN Y 51015-101 51019-101 51019-201 51019-700
PV-ABC 31
E-1234 65.5
Project E 50
Project 3 of 3 29.75
E-ABC 26
P00123 36.25
E-1234 72
Project 3 of 3 135.5

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Replies
5
Views
96
Replies
3
Views
245
Replies
3
Views
228
Replies
2
Views
150
Replies
4
Views
140

1,196,102
Messages
6,013,464
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back