Hello everyone. I wonder if you can help me debug a formula? I need to include several criteria which are parts contained within longer entries. Firstly, let me tell you the criteria; 1-5 are selected from dropdown lists by the user:
1. Month = 11
2. Year = 2011
3. Status = Finished
4. Location = specified location
5. Sex = F
6. Range column contains:
a. cont or
b. GU or
c. implant
I wrote this formula to achieve what I needed:
=SUMPRODUCT((MONTH(attdata_AttendanceDate)=C$3)*(YEAR(attdata_AttendanceDate)=C$2),--(attdata_Status=C$5),--(attdata_Location=C$4),--(attdata_Sex=$B$37)*(OR((NOT(ISERROR(SEARCH("gu",attdata_SessionName)))),(NOT(ISERROR(SEARCH("cont",attdata_SessionName)))),(NOT(ISERROR(SEARCH("implant",attdata_SessionName)))))))
Everything works upto the '(OR(' part, at which point it returns a value of 0, when it should come back with at least 2.
Am I asking too much? I could be specific and take the easy way out of this but that is lengthy, unprofessional and there are good reasons I am trying it this way; flexibility and later inclusion into a management analysis tool. Plus, I'm really trying to push myself with SUMPRODUCT.
It is possible that I've become fixated on this formula and not an alternative way to achieve what I need as well, which is another reason to ask for help here.
Many thanks for reading - I look forward to any replies.
LeonK
1. Month = 11
2. Year = 2011
3. Status = Finished
4. Location = specified location
5. Sex = F
6. Range column contains:
a. cont or
b. GU or
c. implant
I wrote this formula to achieve what I needed:
=SUMPRODUCT((MONTH(attdata_AttendanceDate)=C$3)*(YEAR(attdata_AttendanceDate)=C$2),--(attdata_Status=C$5),--(attdata_Location=C$4),--(attdata_Sex=$B$37)*(OR((NOT(ISERROR(SEARCH("gu",attdata_SessionName)))),(NOT(ISERROR(SEARCH("cont",attdata_SessionName)))),(NOT(ISERROR(SEARCH("implant",attdata_SessionName)))))))
Everything works upto the '(OR(' part, at which point it returns a value of 0, when it should come back with at least 2.
Am I asking too much? I could be specific and take the easy way out of this but that is lengthy, unprofessional and there are good reasons I am trying it this way; flexibility and later inclusion into a management analysis tool. Plus, I'm really trying to push myself with SUMPRODUCT.
It is possible that I've become fixated on this formula and not an alternative way to achieve what I need as well, which is another reason to ask for help here.
Many thanks for reading - I look forward to any replies.
LeonK