0

I would like to know if there is a way in Excel to create a cell that will display a value using a formula that will calculate the latest entry by date and campaign.

If I have 3 columns DATE | CAMPAIGN | COUNT I want to create a formula that will look up the latest date for a campaign and display the value in the count column.

  • Please add some sample data you have and also desired output to make your question more clear. – Máté Juhász Apr 30 '15 at 11:06
0

The question is a little vague. You state that you want the "latest date" in the count column?

If you only want the latest date, Pivot Tables are the way to go or alternatively, possibly the Microsoft PowerQuery addin.

I assume you really want the count to show the total number of records for the campaign but to only show the latest campaign date?

Again, pivot tables will enable this.

Without testing (so I may be totally wrong!):

  • Add CAMPAIGN to the rows
  • Add DATE to the data and change the calculation to MAX instead of count
  • Add CAMPAIGN to the data, make sure the calculation is count and change the column heading to COUNT
0

You could use an array formula that will work with unsorted and repeat dates.

The formula in F2 is

=LOOKUP(2,1/(($B$2:$B$6=E2)*$A$2:$A$6=MAX(IF($B$2:$B$6=E2,$A$2:$A$6,0)))),$C$2:$C$6)

This is an array formula and must be confirmed with Ctrl-Shift-Enter. Then you can copy it down.

Note that in the screenshot the latest date for event A is in the first row of data.

enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.