Hello,
Despite my very limited knowledge of Excel, this website has helped me figure out how to make some pretty complicated formulas. I've spent more than a few hours with this one and I'm getting nowhere. Hopefully someone can help.
These screenshots are from my home computer where I have Excel 2013. The formula I'm trying to figure out is for work and we use Excel 2002. Any suggestions should be for Excel 2002 please.
This spreadsheet is used to track zone audits by day and by zone. Sheet 1 is the daily tracking and Sheet 2 tracks our progress for each zone. This example is a very basic version of what we actually use. What I'm trying to accomplish is in Column D on Sheet 2, I'd like to create a formula which takes the dates in Columns A and B, finds the rows on Sheet 1 that are between those two dates, checks that the "class" (column D on Sheet 1) is "1a" and then adds the values in Sheet 1, Column C that correspond to the dates in Column A. For instance: the formula in D2 on Sheet 2 should check that the date Column A on Sheet 1 is between A2 (1-Jan) and B2 (1-Jan) on Sheet 2, then check that Column D on Sheet 1 is "1a" and then calculate the value in Column C on Sheet 1 which matches to give a result of 0.25.
Another example: Sheet 2, D6 would add Sheet 1's C4 + C6 + C7 because they are between Jan 3 and Jan 5 and also contain "1a." The result would be .5 + 0 + .33 = .83.
I hope all of that makes some sort of sense. I keep running into problems with >= for dates. I really appreciate any help that can be offered.
Sheet 1.jpg
sheet 2.jpg
Bookmarks