need formula help please!

[thereseexceljakubiak]

=SUMPRODUCT(--(B1:B4614<=$N$3),--ISNUMBER(MATCH($A$2:$A$4614,J2:J26,0)))


this formula is not working.


Column A has over 4000 ID Numbers

Column J has a list of 25 ID Numbers


Column B has over 4000 Dates

Cell N3 has A Date


I need a COUNTIF/SUMPRODUCT Where IF the 25 ID Numbers exist in ColumnA, the Count the dates in ColumnD is <= to N3.


THANKS!!

 

[NARAYANK991]

Hi Therese ,


Change your formula to :


=SUMPRODUCT(--(B2:B4614<=$N$3),--ISNUMBER(MATCH($A$2:$A$4614,J2:J26,0)))


The B1 is creating the problem.


Narayan

 

[thereseexceljakubiak]

amazing!! Thank you Narayan !!

 

[Colin Legg]

Here's another way, although it'll handle empty cells in column B differently (it will ignore them):

[pre]

=SUMPRODUCT(COUNTIFS(B2:B4614,"<="&$N$3,$A$2:$A$4614,J2:J26))

[/pre]