COUNTIFS with VLOOKUP

slinky

Active Member
Joined
Dec 19, 2008
Messages
294
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..
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Dec 19, 2008
Messages
294
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Dec 19, 2008
Messages
294

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Dec 19, 2008
Messages
294

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Dec 19, 2008
Messages
294
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

Active Member
Joined
Dec 19, 2008
Messages
294
Have I broken Andrew?

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,681
Messages
5,833,087
Members
430,190
Latest member
beaviss

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top