# Attempting to sumproduct dates

#### thetheaterimp

##### New Member
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.

### 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
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
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
Ah ha! Awesome! Thank you that works perfectly. Going to save me a lot of headache counting everything myself.

Replies
0
Views
147
Replies
4
Views
80
Replies
3
Views
78
Replies
11
Views
51
Replies
3
Views
25

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.

### Which adblocker are you using?

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

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