• 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.

Vlookup and Sumproduct

polarisking

polarisking

Member
I need to count elements in an array where the element qualifies based on a separate lookup.


Here's the horizontal array of 6 items (could be more, of course)


Example:


RPT01 ABC02 RPT03 RPT04 XYZ05 RPT06


For each item, I want to count it ONLY if a lookup vs. a separate table = "Yes" in the second column(example of lookup table below)


RPT01 Yes

RPT02 Yes

RPT03 No

RPT04 Yes

RPT05 Yes

RPT06 No


The answer here would be 2.


I've tried using vlookup inside a sumproduct -Something like =sumproduct(--(vlookup(RptIds,RptIdTable,2,false)="Yes"))), but it's failing. I'm grateful for any advice.
 
You'll need to make sure the lookup table is sorted, but you can then use the LOOKUP function instead of VLOOKUP (LOOKUP can handle array inputs) and use this formula:

=SUMPRODUCT(1*(LOOKUP(RptIds,RptIdTable)="Yes"))
 
Try this

=SUMPRODUCT(--(E2:J2=E5:E10)*(F5:F10="Yes"))


Range E2:J2 is your horizontal array

Range EF:E10 is your vertical RPT List

Range F5:F10 is your Yes/No array.


Worked perfectly for me
 
Which one worked?! :d

We like to know these things. It also helps when other people search for this same sort of problem in a search engine.
 
Montrey, I neglected to note that the # of elements isn't necessarily equal to the number of entries in the lookup table. Hence, the arrays are irregular.
 
I changed the size of the arrays and my formula still works.

Are you still have trouble with you problem?
 
Weird. I have your exact example and I have changed the arrays in every which way and it is still working for me.


For example:

=SUMPRODUCT(--(E2:R2=E5:E65)*(F5:F65="Yes"))


as long as the RPT List and the Yes/no List are the same size. The Horizontal array can be any size.
 
@polarisking

Would you mind copying the exact formula you are using to here so we can see if something's getting changed/copied wrong?
 
I would use this way, with COUNTIF or ISNUMBER/MATCH


=SUMPRODUCT((COUNTIF(RptIds,INDEX(RptIdTable,0,1))>0)*(INDEX(RptIdTable,0,2)="Yes"))


If you want, you can replace INDEX with appropriate single column range.


Regards,

Haseeb
 
You must log in or register to reply here.
Back
Top