Counting overdue dates

Question

Hello all,

Platform: Microsoft 365 web

I have been trying to create a formula for the following:

On a "dashboard" I am tracking several data points from my workbook. I need my dashboard to track if one date is bigger than another date on one of the sheets.

If Column J says "Name" and column AO is bigger than column AN, it should get counted on my dashbaord. I've tried countifs and sum formulas with nested ifs, but none seem to be working. Can anyone lend a hand?

If it helps, for context, my company contracts with outside agencies and has specific report due dates. I am trying to track the agencies and if the reports are coming in on time or are late. The late ones should be tallied on the dashboard for easy reference.

agency name: Column J

Due date: Column AN

Received Date: Column AO

Tally totalled on a seperate "Dashboard" sheet.

Thanks for any help sent my way!

Answer 1

How about

=SUM(('Sheet Name'!$J$2:$J$1000="Name")*('Sheet Name'!$AO$2:$AO$1000>'Sheet Name'!$AN$2:$AN$1000))

where Sheet Name is the name of the sheet with the data. Adjust the ranges if necessary.

Answer 2

Same as this question on Microsoft Tech Community