VLookUp and count 1

[katoc]

Hello.

I am trying to do a count and a vlookup at the same time in Excel.

I have a worksheet (info):
ID Date Server
Leader 1 1/8/2010 EG
Leader 3 1/8/2010 EG
Leader 1 1/9/2010 GoM
Leader 1 1/10/2010 GoM
Leader 2 1/11/2010 EG
Leader 4 1/12/2010 EG

There is another worksheet (Leader):
id version
Leader 1 TRUE
Leader 2 TRUE
Leader 3 FALSE
Leader 4 FALSE

these are obviously simplified for my question. What I want to do is:
Find the count of instances in Sheet Info where Server = EG and corresponding leader = True (from leader sheet). In this case it should be 2. I started with sumproduct, but I need to use Vlookup for each leader to get the Version value but I can't figure out how to combine them to get a count.

This is what I have so far:
=SUMPRODUCT(--(Info!C:C="EG"), --(Leader!B:B = "TRUE"))
obviously the leader portion is wrong. The vlookup should be: =VLOOKUP(Info!A2, Leader!A:B, 2, 0) for the first value which would return TRUE, but I need the count of all VLOOKUPS that return a TRUE for EG"

 

[SkipVought]

Hi,

Make a column on sheet info for the lookup of ID on sheet leader to return version.

Then do your SUMPRODUCT on the data in info

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[katoc]

i've been avoiding having to add a column to Info. Is there no other way?

 

[SkipVought]

How else do you propose to associate the ID values?

One other technique would be MS Query. Joint the 2 tables, assign the criteria and return a count.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I didn't have my system available when I answered previously regarding MS Query, but I got the result of 2 in about 30 seconds, using this SQL...

SELECT Count(*)
FROM `Info$` `Info$`, `Leader$` `Leader$`
WHERE `Info$`.ID = `Leader$`.ID AND ((`Info$`.Server='EG') AND (`Leader$`.Version=true))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[madonnac]

ok. I finally worked out a formula, and it looks horrible, but it works

This has to be entered as an array formula, using CTRL + SHIFT + ENTER after entering it into a cell.

=SUMPRODUCT(--(IFERROR(LOOKUP((IF(Info!$C$2:$C$7="EG",Info!$A$2:$A$7)),Leader!$A$2:$A$5,Leader!$B$2:$B$5),0)))

there will be {} around the formula if you've successfully entered it as an array formula

the innermost IF will check for "EG" in the 3rd column, and return the list of leaders from the first column, or FALSE if there's no EG in the data.
LOOKUP will then check the list from the IF, and return the corresponding TRUE/FALSE from the table, or #N/A if there's no leader listed (usually from the FALSE from the previous operation).
IFERROR will change all the #N/A to zero
-- will change all the TRUE/FALSE to 1/0
SUMPRODUCT will add up the numbers, and return the total

 

[katoc]

Madonnac,

THANK YOU SO MUCH!!! that is exactly what I needed and it worked perfectly. i added a few more criteria (in addition to checking for 'EG') and it's just perfection. Thanks!!! I really owe you one!