# Thread: Find Latest value that meets Criteria Thanks:  1 Post #5325824 (1) Likes:  4 Post #5325017 (1)Post #5325824 (1)Post #5324495 (1)Post #5324497 (1)

1. ## Find Latest value that meets Criteria

I am wondering how can i find the latest information that meets my criteria, because all my table are run form a formula we the "Blank" cells will always read as a')', there fore i am having a Countif, Sumif, Maxif, etc issues, i am probably just typing my formula incorrectly.

Based on the following table, I need the Red X to equal '5'. Lets say for example i have already collated my data and all I am looking at is Salesmen 'John', lets also preten that John made not sales in Month 3 because he was on vacation, but all i want to know when that last sale was made in this case the '5th' month was his latest sale. as stated all cells will have numbers in them as that infor is fed through other formulas, as you know if a formula returns 'Nil' as a result then a '0' is placed in the sell

 Name John Lates sale made 'x' 'th Month Name Month Sales Made Sales (\$) John 1 1 100 John 2 5 200 John 3 0 0 John 4 2 200 John 5 4 300 John 6 0 0

2. ## Re: Find Latest value that meets Criteria

Hi, Try Below:

ABCD
1NameJohn
3
5John11100
6John25200
7John300
8John42200
9John54300
10John600

Sheet1

Worksheet Formulas
CellFormula
C2=AGGREGATE(14,6,(\$A\$5:\$A\$10=\$C\$1)*(\$C\$5:\$C\$10>0)*(\$B\$5:\$B\$10),1)

3. ## Re: Find Latest value that meets Criteria

Perfect, i like it.

4. ## Re: Find Latest value that meets Criteria

Thanks @Sam_D_Ben

5. ## Re: Find Latest value that meets Criteria

=LOOKUP(2,1/((C5:C10 > 0)*(A5:A10=C1)),(B5:B10))

6. ## Re: Find Latest value that meets Criteria

Another option:

=MAXIFS(B5:B10,A5:A10,C1,C5:C10,">"&0)

7. ## Re: Find Latest value that meets Criteria

Thank you So much! all 3 options work perfectly!,

#1. =AGGREGATE(14,6,(\$A\$5:\$A\$10=\$C\$1)*(\$C\$5:\$C\$10>0)*(\$B\$5:\$B\$10),1)
- By Aryatect

#2. =LOOKUP(2,1/((C5:C10 > 0)*(A5:A10=C1)),(B5:B10))
-
By DanteAmor

#3. =MAXIFS(B5:B10,A5:A10,C1,C5:C10,">"&0)
- By Aryatect

Now depending how complex the overall task is, with over 100 salesmen across 12 different sheets I will try them all and see which works best for larger data bases

8. ## Re: Find Latest value that meets Criteria

Glad we could help and thanks for the feedback !