-1

I have an excel spread sheet with two columns:

A               B
01/04/2015      Riddor
27/03/2015      Riddor
02/04/2014      Other Text

In my cell i am trying to create a function which will check column b to see if the word 'Riddor' exists and if it does check the corresponding date in column A to find out which entry of my value 'Riddor' is the most recent.

For instance in the example above you see two occurrences of the value 'Riddor' one entry for 27th march and the other for yesterday. I want my function to count the number of days since the most recent entry of 'Riddor' was made.

So this would give me a result of 1 days ago because the last entry of 'Riddor' was yesterday's date.

Can someone please show me where i am going wrong with this? Thanks

I have tried this:

=COUNTIF(B:B,"Riddor",MATCH(MAX(A:A),B:B,"Riddor"))

and i have tried this:

{=IF(COUNTIF(B:B,"Riddor")=0,"No Match",IF(COUNTIF(B:B,"Riddor")=1,INDIRECT("A"&MATCH("Riddor",B:B,0)),MAX(IF(B:B="Riddor",A:A))))}

neither give me the right result. I'd appreciate any help, thanks in advance

James Gayle
  • 157
  • 1
  • 1
  • 12

1 Answers1

0

Please try the following:

=COUNTIF(A:A,">"&MAX(IF(B:B="Riddor",A:A)))

Basically, i'm doing a MAXIF to check the biggest value, IF="Riddor", and then, count all the dates that are bigger than that. It's an array formula, so apply it with CTRL+SHIFT+ENTER

IgorKB
  • 38
  • 1
  • 6