Attempting to sumproduct dates

[thetheaterimp]

So I want to be able to count all instances of a persons name in Column A that have a date of a week old in Column B.

I know I can accomplish just the date counting with this formula:
=COUNTIF(B:B,"<="&(TODAY()-7))

Though I dont know how to expand on this so it only counts those that meet that criteria for a persons name.

Thanks in advance!

 

[Jonmo1]

Welcome to the board..

Which version of Excel do you have?

XL2003 or earlier, you need sumproduct
=SUMPRODUCT(--(A1:A1000="SomeName"),--(B1:B1000<=TODAY()-7))
Note, that you can't use entire column refs like A:A, must use specific row#s like A1:A1000

XL2007 or higher, you can use COUNTIFS

=COUNTIFS(A1:A1000,"SomeName",B1:B1000,"<="&TODAY()-7)
In XL2007 you CAN use entire column refs, but it's still not recommended.


Hope that helps.

 

[barry houdini]

Hello thetheaterimp, welcome to MrExcel

Which version of excel are you using?

In excel 2007 and later versions you can use COUNTIFS, e.g.

=COUNTIFS(A:A,C1,B:B,"<="&TODAY()-7)

where C1 contains a specific name

....or in Excel 2003 and earlier try SUMPRODUCT with a restricted range

=SUMPRODUCT((A$2:A$100=C1)*(B$2:B$100<=TODAY()-7))

 

[thetheaterimp]

Ah ha! Awesome! Thank you that works perfectly. Going to save me a lot of headache counting everything myself.