CODEWITHSUNDEEP
excelexcel-formula
user8346348
user8346348

Reputation:

How do I count only the latest values if they're greater than a number?

I have 3 columns:

ID     Date     Value
100    1/1/17   2
200    1/2/17   3
100    2/1/17   1

I need a formula to count if the latest date has a value greater than or equal to 2

The result in the above example would be:

ID     Count
100    0
200    1

Because while on 1/1/17 the value was 2, the later value on 2/1/17 was 1 so 0.

Upvotes: 1

Views: 209

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

Assuming your values are in columns A-C in your example, generate a list of unique ID in column D. Assuming your first unique ID is in D1 place the following formula in a blank cell and copy down.

 =SUMPRODUCT((D1=A:A)*(AGGREGATE(14,6,(D1=A:A)*B:B,1)=B:B)*(C:C>=2))

sumproduct and aggregate are both function that perform array like operations inside their (). With the aggregate function the maximum date for the ID is being determined. I am assuming this is what you mean by latest date. That maximum date is then being used with the sumproduct. sumproduct is performing a row by row check to see if various logical checks are true (value of 1) or false (value of 0). The * operators are behaving like an AND statement inside the sumproduct. As such, only rows that match the ID number AND have the maximum date for that ID and have a value of 2 or more in the third column are true. When multiplying true by true you get a value of 1. when multiplying true by false or false by false you get 0. Sumproducts last step is to sum the 1s and 0s to give you your count.

Formulas used:

  • SUMPRODUCT
  • AGGREGATE

Upvotes: 1

Related Questions