Max Formula

Samuel Dowdy Jr.

=IFERROR(MAX(INDEX({Customer Invoice Log Sheet - Days Past Due}, MATCH([Profit Center]@row, {Customer Invoice Log - Profit Center}, 0))), "")

Im looking for the max days for the profit center. Problem is its giving me the answer 0 when it should be more than that. In some cases it seems to be working. Its like its pulling the first profit center row total days when it should be looking at the whole sheet

Jason Tarpinian

Are you trying to return the MAX {Customer Invoice Log Sheet - Days Past Due} value? The MAX you have outside of the INDEX isn't doing anything, as INDEX is only going to return a single value, you want MAX to look at a range.

Try using the formula below. The COLLECT function will return a range of Days Past Due for only the Profit Centers that match your @row, and then run the MAX function on that custom range to return the max Days Past Due.

=MAX(COLLECT({Customer Invoice Log Sheet - Days Past Due}, {Customer Invoice Log - Profit Center}, [Profit Center]@row))