Help with a count/vlookup formula

[TechRetard]

Sheet 1: Primary Key, column A....wanted formula in column B

1	[Formula]
2	[Formula]
3	[Formula]

Sheet 2: Data columns A-E, column A is primary key, B-E are either blanks or dates

1	41000	41500
2
3	41001

The formula looks at the key on sheet 1, looks it up, and then counts how many "dates" are the columns on sheet 2 corresponding to the key

example: for 1, the formula would return 2, because there are 2 dates listed for #1 on the second sheet
example2: for 2, the formula would return 0, because there are no dates listed for #2 on the second sheet

...thanks in advance

[Punnam]

Hi,

Check this

Punnam

[cs454]

If you have the same ascending Primary Key in both worksheets why dont you just use a simple count formula?
In cell B1

Please Login or Register  to view this content.

and drag down.

[Olly]

In B2:

Please Login or Register  to view this content.

Copy down.

[Punnam]

Alternative with Indirect + Countif+match paste it in b2
=IFERROR(COUNTIF(INDIRECT("Sheet2!"&"B"&(MATCH(A2,Sheet2!$A$2:$A$4,0)+1)&":"&"E"&(MATCH(A2,Sheet2!$A$2:$A$4,0)+1)),"<>"&""),"")
Punnam

[TechRetard]

Close... but I can't hard code in just one row on the Countif because I don't know which row that is...see modified exampleIf + Countif+Array_TR.xlsx

[Punnam]

Hi ,
Check with my another post 5,
=IFERROR(COUNTIF(INDIRECT("All Data!"&"B"&(MATCH(A2,All Data!$A$2:$A$11,0)+1)&":"&"E"&(MATCH(A2,All Data!$A$2:$A$11,0)+1)),"<>"&""),"")
it will take care .Just Replace Sheet2 with the new sheet name Checked & works fine for me
Punnam

[TechRetard]

Olly/Cs454, thanks for taking a moment to help me out ... the example was just for simplicity, it's not a 1 to 1 row match as i tried to show in my modified example via .xlsx above. Looking for something a little more robust as sheet 2 is the 'all data' tab and sheet 1 will just be a summary, where the primary key #s may change depending on other variables... in actuality my workbook is about 50 columns and hundreds of rows

[TechRetard]

Hi ,
Check with my another post 5,
=IFERROR(COUNTIF(INDIRECT("All Data!"&"B"&(MATCH(A2,All Data!$A$2:$A$11,0)+1)&":"&"E"&(MATCH(A2,All Data!$A$2:$A$11,0)+1)),"<>"&""),"")
it will take care .Just Replace Sheet2 with the new sheet name Checked & works fine for me
Punnam

Got it to work, thank you sir

[Punnam]

Hi TechRetard ,

Thanks for the reps. u r welcome.

Punnam