Countif cells within a week

[conroar]

Hi, I'm working on a project and am trying to get a function to do a lookup. If Date(A) is within the past 7 days and (C) is "New York" then it counts towards the total.



A B C
1 Date | Name | Location

2 17/07/2017| Bob | New York
3 09/07/2017| Steve | New York
4 11/07/2017| Larry | New York
5 17/07/2017| Jeff | Mumbai


The outcome should say "2". As C2,C3 & C4 all say "New York" but A3 is not within the past 7 days.

Regards,

 

[Special-K99]

=countifs(a1:a4,">="&today()-7,c1:c4,"ny")

 

[arthurbr]

Perhaps =SUMPRODUCT((A2:A5>(TODAY()-7))*(A2:A5<TODAY())*(C2:C5="New York"))
You might want to replace > by >= or < with <= if need be.
Adapt range as needed

 

[conroar]

Thanks both, used both answers together to get a good working formula!