# IF criteria is met count ?

#### NSRaceway

##### New Member
A B C
1 Today's Date

2 BMW London 2/11/18

3 Honda Manchester

4 BMW Liverpool 4/4/17

Required Return - IF A2 = BMW & IF B2 = London & IF within today's date -365 Days - Return Count 1

Thank you.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Joe4

Welcome to the Board!

Try something like:
Code:
``=IF(AND(A2="BMW",B2="London",TODAY()-C2<=365),1,0)``

#### NSRaceway

##### New Member
Thank you Joe4, but i got as per below.

#### Joe4

I thought you were wanting to do one row at a time, not the whole range. You cannot use IF on a range of cells like that.
In that case, try:
Code:
``=SUMPRODUCT(--(A3:A7="BMW"),--(B3:B7="London"),--(C3:C7>=TODAY()-365))``

#### NSRaceway

##### New Member
Had i not been an idiot and posted the picture of what i had and wanted more clearly i could have saved you 5 mins Sorry my bad

Sir, you are a GENIUS, can not THANK YOU enough.

#### Joe4

No worries! Glad we got it working for you!

Note that there is also a COUNTIFS function that would typically work on these sort of things, but that last date condition is a little tricky, so I elected to use SUMPRODUCT instead.

#### NSRaceway

##### New Member
Sorry Joe4, one last Question on this Please.

With what you have given me, will show our Car and Town in Last 365 Days (Year) What would the Formula be for C9 to count Car, Town, AND either No Date &/or Earlier dates.

The hope from this is Two sets of figures, the total number of time BMW have been to a town in the last 12 months & total number of times BMW have been to a town and/or recorded to go but no date as yet.

Long term is to turn these figures into graphs, to account for 8 Car Variants & 32 Towns

Thank you

#### Joe4

Just try changing the last condition to:
Code:
``(C3:C7 < TODAY()-365))``
<today()-365))[ code]<="" html=""></today()-365))[>

Last edited:

Thank you

You are welcome.