Attempting to sumproduct dates

thetheaterimp

New Member
Joined
Oct 24, 2011
Messages
2
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!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Oct 24, 2011
Messages
2
Ah ha! Awesome! Thank you that works perfectly. Going to save me a lot of headache counting everything myself.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top