sum two col one of which is a date.

  • Thread starter Thread starter MusicMan
  • Start date Start date
M

MusicMan

I have two col. one with names, the second with dates.

I need a COUNT of a specific persons dates before xx/xx/xx.

I can count either with countif but fail when I try to combine.
 
=SUMPRODUCT(--(A2:A7="john"),--(B2:B7<A1))
where john is the name youre searching for and cell a1 contains the date you
want it to fall before.
 
Hi MusicMan,

You can try

Given in column A the names and in B the dates, in H1 your reference date.

=SUM((A1:A4="JP")*(B1:B4<=H1))

You have to enter this as an array function: ctrl+shift+enter.

Wkr,

JP
 
If you're using Excel 2007...

A2:A25 = names
B2:B25 = dates

D2 = target date
E2 = some name

=COUNTIFS(A2:A25,E2,B2:B25,"<"&D2)
 
Back
Top