IF criteria is met count ?

NSRaceway

New Member
Joined
Mar 1, 2019
Messages
5
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,083
Office Version
365
Platform
Windows
Welcome to the Board!

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,083
Office Version
365
Platform
Windows
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
Joined
Mar 1, 2019
Messages
5
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,083
Office Version
365
Platform
Windows
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
Joined
Mar 1, 2019
Messages
5
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,083
Office Version
365
Platform
Windows
Just try changing the last condition to:
Code:
(C3:C7 < TODAY()-365))
<today()-365))[ code]<="" html=""></today()-365))[>
 
Last edited:

Forum statistics

Threads
1,089,472
Messages
5,408,447
Members
403,205
Latest member
DeB395

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top