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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Welcome to the Board!

Try something like:
Code:
=IF(AND(A2="BMW",B2="London",TODAY()-C2<=365),1,0)
 
Upvote 0
Thank you Joe4, but i got as per below.

Stuck%20in%20Excel.webp
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Just try changing the last condition to:
Code:
(C3:C7 < TODAY()-365))
<today()-365))[ code]<="" html=""></today()-365))[>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top