• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT and soundlikes - Help

LeonK

New Member
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
 
Instead of using an OR function, you can add the arrays together (as it will be either 1 or the other).

=SUMPRODUCT((MONTH(attdata_AttendanceDate)=C$3)*(YEAR(attdata_AttendanceDate)=C$2),--(attdata_Status=C$5),--(attdata_Location=C$4),--(attdata_Sex=$B$37)*((ISNUMBER(SEARCH("gu",attdata_SessionName)))+(ISNUMBER(SEARCH("cont",attdata_SessionName)))+(ISNUMBER(SEARCH("implant",attdata_SessionName)))))


Also, if you want to add some robustness, you could replace the MONTH and YEAR functions with TEXT. Advantage is that if a text string gets fed into the TEXT function, it won't cause an error, and you can do them both at once.


=SUMPRODUCT((TEXT(attdata_AttendanceDate,"mmyyyy")=C$3&C$2)*(attdata_Status=C$5)*(attdata_Location=C$4)*(attdata_Sex=$B$37)*((ISNUMBER(SEARCH("gu",attdata_SessionName)))+(ISNUMBER(SEARCH("cont",attdata_SessionName)))+(ISNUMBER(SEARCH("implant",attdata_SessionName)))))
 
Luke,


That solution is absolutely great. Thank you so much. I could not have come up with that on my own.


Additionally, I love that you have condensed the date process with the TEXT function, streamlining the formula.


I'm learning a lot from your example and will be looking at my other sheets to see where I can incorporate both the TEXT and ISNUMBER
functions.


Again - Many many thanks and Merry Christmas.


LeonK.
 
Luke,


After playing with your solution, I began to wonder if it would be possible to select from a range rather than specifying the criteria within the ((ISNUMBER)) arguement?


The reason for this is that other values could exist and would be missed if not included into the formula at the time they were added to the dataset.


I will look into this once I have rewritten the book for which I needed your help (it is undergoing a complete rebuild to incorporate as much as I have learnt from the Excel School thus far). However, I thought I would also ask you, or anyone else that is interested, as you may already have done something similar and could save me even more time than you already have.


Kind Regards - LeonK
 
You're very welcome LeonK. =)

I'm not sure about using a range. If you were looking for an exact match, you could do this using the MATCH function.

=SUMPRODUCT(--(ISNUMBER(MATCH(KeyWords,ListOfWords,0))))


For searching for a value within a range, I think you'll have to use an UDF. =/
 
Back
Top