• 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 to lookup based on 2 criteria, ignoring 2nd criteria if not needed

C

chholland

New Member
I have a table of data with two columns (A=code 1) and (B = code 2). I have set up a dictionary that returns a category based on the combination of the codes in column A and B. This data dictionary is held for example as (D = code 1), (E = code 2), (F = category). This works fantastic using sumproduct as follows"


=SUMPRODUCT((D2:D10=A2)*(E2:E10=B2)*(F2:F10))


However, in some cases it does not matter what the second code is (code 2) as there is only one category to return based on the first code. In this case, the field for code 2 (column E) would be blank. SUMPRODUCT returns 0 in these instances and not the appropriate category from column F. I have tried entering a wildcard "*" or "N/A" instead of the blank, but it still does not return the correct category.


I can provide sample data if this is unclear. Essentially Code 2 only needs to be looked up, if there is more than 1 entry for code 1 in the dictionary. Thank you very much in advance for any help or direction.


Chris
 
Chris


Firstly, Welcome to the Chandoo.org forums.


In the case where column E is blank and that occurs when some value of Code 1 is met you can use something like

=SUMPRODUCT((D2:D10=A2)*IF(A2=Value,(E2:E10=B2),1)*(F2:F10))

or

=SUMPRODUCT((D2:D10=A2)*IF(A2<>Value,(E2:E10=B2),1)*(F2:F10))


Without specific examples it is a bit hard to go much further


To post sample files refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thank you so much for the help. I have uploaded a test example of the data at: https://dl.dropbox.com/u/84788799/Test.xlsx


The blanks are not in the data, but in the lookup dictionary. For clarity, for some codes in code 1, the second code could be anything and does not matter. For others, the second code is important in determining the category. An example


Code 1 = 100, Code 2 = any code, then Category = X

Code 1 = 150, Code 2 = 6, then Category Y

Code 1 = 150, Code 2 = 8, then Category Z


Dictionary would look like

Code 1 Code 2 Category

100 X

150 6 Y

150 8 Z


The excel file linked to this post has some complete example dataset.


Thanks again for the help,


Chris
 
Hello Chris,


Try this in C5 and copy down.


=SUMPRODUCT((E$5:E$10=A5)*ISNUMBER(SEARCH(TEXT(F$5:F$10,"0;-0;?"),B5)),G$5:G$10)
 
You must log in or register to reply here.
Back
Top