Vlookup with nested IF's or CountIF's

**jj72uk** · 10-08-2009, 07:40 AM

Hi guys, hopefully you can shed some light onto this small problem I am having.

The current formula I'm trying to use is:

=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(FAIL,""))

This isn't producing the desired results.

What I am trying to do in words is this:

IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail

I think I've gotten the syntax mixed up...can anyone guide me?

**NBVC** · 10-08-2009, 07:59 AM

Maybe?

=SUMPRODUCT((EPG1!D4:D26=A4)*(EPG1!I4:I26={"Pass","Fail"}))

this counts how many times Pass or Fail exists in column I, where Column D contains value found in A4

**jj72uk** · 10-08-2009, 08:19 AM

This would work, but the problem is the reason I was using vlookup is becasue I want to correspond that that match

If I use simproduct it will add all the PASS/FAILS which might be a pass/fail for someone else.

**jj72uk** · 10-08-2009, 08:23 AM

My mistake, works perfectly....

**jj72uk** · 10-08-2009, 08:52 AM

Thank you for your great help.. I've entered in the formula...As you'll see below...

Is there any way to compact this?

=SUMPRODUCT((EPG1!D$4:D$26=A4)*(EPG1!F$4:F$26={"Pass"}))+SUMPRODUCT((EPG1!D$4:D$26=A4)*(EPG1!G$4:G$26={"Pass"}))+SUMPRODUCT((GAW4!D$4:D$26=A4)*(GAW4!F$4:F$26={"Pass"}))+SUMPRODUCT((GAW4!D$4:D$26=A4)*(GAW4!G$4:G$26={"Pass"}))+SUMPRODUCT((KT16!D$4:D$26=A4)*(KT16!F$4:F$26={"Pass"}))+SUMPRODUCT((KT16!D$4:D$26=A4)*(KT16!G$4:G$26={"Pass"}))+SUMPRODUCT((KF10!D$4:D$26=A4)*(KF10!F$4:F$26={"Pass"}))+SUMPRODUCT((KF10!D$4:D$26=A4)*(KF10!G$4:G$26={"Pass"}))+SUMPRODUCT((WLBC!D$4:D$26=A4)*(WLBC!F$4:F$26={"Pass"}))+SUMPRODUCT((WLBC!D$4:D$26=A4)*(WLBC!G$4:G$26={"Pass"}))+SUMPRODUCT((BROP!D$4:D$26=A4)*(BROP!F$4:F$26={"Pass"}))+SUMPRODUCT((BROP!D$4:D$26=A4)*(BROP!G$4:G$26={"Pass"}))+SUMPRODUCT((SALX!D$4:D$26=A4)*(SALX!F$4:F$26={"Pass"}))+SUMPRODUCT((SALX!D$4:D$26=A4)*(SALX!G$4:G$26={"Pass"}))+SUMPRODUCT((JK09!D$4:D$26=A4)*(JK09!F$4:F$26={"Pass"}))+SUMPRODUCT((JK09!D$4:D$26=A4)*(JK09!G$4:G$26={"Pass"}))

**NBVC** · 10-08-2009, 09:58 AM

If you download and install a free addin from here: Morefunc.xll

You can use:

=SUMPRODUCT((THREED(EPG1:JK09!D$4:D$26)=A4)*(THREED(EPG1:JK09!F$4:G$26)="Pass"))

Note: EPG1 is first sheet, JK09 is last sheet...

Note also: This addin is embeddable in the workbook so that you can share it without having to reload the addin elsewhere.

**jj72uk** · 10-08-2009, 10:40 AM

I love it!!

**NBVC** · 10-08-2009, 10:53 AM

Great.. I am glad that works for you.

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save