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
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
