COUNTIFS with VLOOKUP

[slinky]

Perhaps a misleading title, but here's the formula I want to use...

=COUNTIFS(APM!B:B,Main_Sheet!B6,VLOOKUP(APM!A:A,INFO!A:B,2,0),">="&I1-31)

(ignore the fact that it generates an error, you should get the gist of what I'm attempting to do from the layout of it)..

The issue is, of course, that VLOOKUP will only return the first result that it finds, this is an issue as I'm attempting to carry out that lookup on a table that contains approx 1000 rows..

This is all in the name of simplifying an import process to remove the requirement to generate VLOOKUPs for date information from the INFO tab..

Info Contains the following :

A B C
1 ID DATE SERVER
2 1 01/08/2010 FISH
3 2 01/08/2010 FISH


APM Contains the following
A B
1 ID VERSION
2 1 5.00
3 2 4.00


Main_Sheet contains the formula I'm attempting to use..
I1 =TODAY()
B6 = 5.00

brain hurts.. I'm sure I should be using something along the lines of Index/Match/Offset to get what I need, but I'll be damned if I can fathom how to use them today!

Shout if you need any more info..

TIA..

 

[Andrew Poulsom]

I can't test this, but if the ID is the same on each row on both sheets try:

=COUNTIFS(APM!B:B,Main_Sheet!B6,INFO!B:B,">="&I1-31)

 

[slinky]

I can't test this, but if the ID is the same on each row on both sheets try:

=COUNTIFS(APM!B:B,Main_Sheet!B6,INFO!B:B,">="&I1-31)

Thanks for taking the time to suggest an answer, unfortunately the result isn't correct.. (it returns 0 when I know the answer should be more like 554)..

I've uploaded the file to the link below if anyone would be so kind as to take a look?

http://drop.io/s4waxnx

TIA..

 

[Andrew Poulsom]

You have:

COUNTIFS(APM!B:B,Main_Sheet!B6,Info!B:B,">="&I1-31)

Column B on sheet APM contains dates while B6 on Main_Sheet contains 6.30L. Shouldn't it be?

COUNTIFS(APM!C:C,Main_Sheet!B6,Info!B:B,">="&I1-31)

 

[slinky]

I touch of "wood for the trees" there I think..

So, it looks like we're a step closer, unfortunately, the result that this change brings isn't accurate..

Applying a filter to APM to filter the date column (which, ultimately will be removed if this is successful) for "after 04/07/2010" and the Version column to return only "6.30L" provides 554 results.. The formula suggested provides 468..

I'm struggling to work out where the missing 86 results are?

Thanks again..

 

[Andrew Poulsom]

Well, your formula is using 2 worksheets so, as I said before, the ID on each row must be the same. But APM has data in rows 2:815 whereas Info has data in rows 2:1014. This pre Excel 2007 formula also returns 468:

=SUMPRODUCT(--(APM!C2:C1014=B6),--(Info!B2:B1014>=I1-31))

 

[slinky]

Hmmmmmm....

Info will always have more rows than any of the other sheets, as not all IDs referenced in Info subscribe to the products listed in, APM, BW, etc.. Apologies, I misunderstood your question about whether the IDs will always be the same..

I guess at this point a change of tack is required.. Is what I am attempting to achieve possible? As I said earlier, the ultimate aim of this formula is to simplify the data import process (population of all worksheets other than Main_sheet), reducing each of those sheets to two columns without the (in my eyes) unnecessary VLOOKUP to return the date field..

Cheers..

 

[Andrew Poulsom]

Since the dates seem to be the same for each ID on both sheets why not?

COUNTIFS(APM!C:C,Main_Sheet!B6,APM!B:B,">="&I1-31)

 

[slinky]

Since the dates seem to be the same for each ID on both sheets why not?

COUNTIFS(APM!C:C,Main_Sheet!B6,APM!B:B,">="&I1-31)

Purely because that is the row I intend to remove..

APM!B is populated via a VLOOKUP that I no longer wish to use, I see the data duplication across all sheets is unnecessary, and that is what I'm trying to avoid..

 

[slinky]

Have I broken Andrew?

I'm sure that this must be possible, or at least I hope it is!