Find Latest value that meets Criteria

[HeyItsDizzy]

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

<tbody>
</tbody>

 

[Aryatect]

Hi, Try Below:

Book1
	A	B	C	D
1		Name	John
2		Lates sale made	5	'th Month
3
4	Name	Month	Sales Made	Sales ($)
5	John	1	1	100
6	John	2	5	200
7	John	3	0	0
8	John	4	2	200
9	John	5	4	300
10	John	6	0	0
Sheet1

Cell Formulas
Range	Formula
C2	=AGGREGATE(14,6,($A$5:$A$10=$C$1)*($C$5:$C$10>0)*($B$5:$B$10),1)

 

[Sam_D_Ben]

Perfect, i like it.

 

[Aryatect]

Thanks @Sam_D_Ben

 

[DanteAmor]

How about:

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

 

[Aryatect]

Another option:

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

 

[HeyItsDizzy]

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

 

[Aryatect]

Glad we could help and thanks for the feedback !

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.