I have a pretty involved syntax:
=SUMPRODUCT(($H$16:$H$838<=140)*($H$16:$H$838>=70)*($M$16:$M$838="Interval 1")*($F$16:$F$838=(MIN($F$1:$F$5000)))*$N$16:$N$838)
The problem I am having is with the part of the program ($F$16:$F$838=(MIN($F$1:$F$5000))). The min function in this case refers to days, where I had renamed/recoded Mon through Sunday with numbers (Sun = 0, Mon = 1 and so on). Now I have a problem, I have to enter data based on the day, but the first day doesn't necessarily correspond with the coded day.
For example, for one set of data ( I have to copy this formula across many different files and the 1st day are not the same between files) the coded day/renamed days however are still Sun=0, Mon=1 and so on.
First Day Mon
Second Day Tue
third Day Wed
But for another file
First Day Sat
Second Day Sun
Third Day Mon
I hope you get the idea.
Originally I had it set up where it was taking the lowest number and then the second lowest and so on, which worked well. However, then I found out that Saturday and Sunday were also included and that no longer works because if one data files Day 1 is Sat, which is coded by me as 6, then I dont know how to make it say that Sun (coded as 0) is day 2.
I know that was long winded but i really could use any help I can get since I have been trying to figure this out for 2 days now.
Bookmarks