Excel formula help

Question

Hi! Hoping to get some help creating a formula.

My data set includes an insurance company, CPT code, and a certain date. Anytime the insurance company and CPT in a given row match the same insurance company and CPT in any other row within the spreadsheet, I need to calculate 90 business days from the most recent date listed in all combinations.

For example:

Row 2 lists ABC Insurance and CPT code 12345 with a date of 1/1/2025.

Row 50 lists ABC Insurance and CPT code 12345 with a date of 2/1/2025

Row 128 lists ABC Insurance and CPT code 12345 (a match with rows 2 and 50). I need the formula to identify 2/1/2025 as the most recent date associated with that insurance and CPT combo and add 90 days to it and display it in a new column on row 128.

Answer 1

You are welcome. Check your image very carefully. You have only shown one date int he result column. Show the expected result very clearly. Also, share data in a format that can be pasted in an MS Excel file.

Answer 2

Excel 365 Pro Plus with Power Pivot and Power Query. Not Mac.

Add 90 days to max date of Insurance and Cpt combo reps.

No formulas, no VBA macro.

https://www.mediafire.com/file_premium/7pza1g1i3ttcxgj/03_15_25.xlsx/file

https://www.mediafire.com/file_premium/jnzrfxjaj12h8ab/03_15_25.pdf/file

Answer 3

Hi,

In cell D2, enter this formula and drag down

=LET(ins,$A$2:$A$7,cpt,$B$2:$B$7,tot,COUNTIFS(ins,A2,cpt,B2),IF(AND(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=tot,tot>1),MAXIFS(C$2:C2,A$2:A2,A2,B$2:B2,B2)+90,""))

Hope this helps.

Answer 4

I'm not targeting a certain insurance company, Bob. To be clear, for leach line, I need the formula to "look back" to see if any same combinations exist of ins+cpt...then within those results, look for the most recent date and add 90 days to it.

In the below example there is a match of Cigna+01400 two times before line 6. So within those combinations, the most recent date is 2/15. We add 90 days to that to get E6 value of 5/16.

Answer 5

Use the MAXIFS function. In this example, C1:C4 is the range of Dates, A1:A4 is the range of ins companies, B1:B4 is the range of codes. Replace the range references with the ones in your worksheet:

=MAXIFS(C1:C4,A1:A4,"ABC",B1:B4,12345)+90