Find Latest value that meets Criteria

HeyItsDizzy

New Member
Joined
Apr 11, 2018
Messages
10
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

NameJohn
Lates sale made'x''th Month
NameMonthSales MadeSales ($)
John11100
John25200
John300
John42200
John54300
John600

<tbody>
</tbody>
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi, Try Below:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Name</td><td style=";">John</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Lates sale made</td><td style="text-align: right;;">5</td><td style=";">'th Month</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Name</td><td style=";">Month</td><td style=";">Sales Made</td><td style=";">Sales ($)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">John</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">John</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">John</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">John</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">John</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">John</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">14,6,(<font color="Red">$A$5:$A$10=$C$1</font>)*(<font color="Red">$C$5:$C$10>0</font>)*(<font color="Red">$B$5:$B$10</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,566
Office Version
2007
Platform
Windows
How about:

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

HeyItsDizzy

New Member
Joined
Apr 11, 2018
Messages
10
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,566
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,084,850
Messages
5,380,276
Members
401,660
Latest member
Zakariyya Ibrahim

Some videos you may like

This Week's Hot Topics

Top