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

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,891
Messages
5,514,004
Members
408,980
Latest member
Naomi_

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top