Formula to total amount of past due based on date in cell

[halloyd]

Hi there,

Trying to get a total count of past due claims based on a due date specified in a cell. Each due date for each claim is different.

Here's a screenshot of what I'm working with:

So basically, I want it to look at the "Due Date" column (H3:H53), compare it to the "Compltd" column (A3:A53), and return the number of claims that were completed late.

Thank you. Let me know if you need any additional explanation.

 

[Marcelo Branco]

Welcome to Mr Excel

See if this does what you need
=SUMPRODUCT(--(A3:A53>H3:H53))

M.

 

[halloyd]

Welcome to Mr Excel

See if this does what you need
=SUMPRODUCT(--(A3:A53>H3:H53))

M.

Yes, this seems to be working when each column has a value. Thank you!

Question: The way this workbook is set up is so we have an active tab and then tabs for each month for completed claims. The active tab does not have a completed date, and is instead filled as "'-". This formula is not counting these correctly. Is there another formula that would allow us to compare today's date with due date to give the same results? Should I just put something in the completed column that calculates today's date?
EX:

Also, I would like to extend this down to around line 300 so that if that many lines are added they are included in the formula. However, if a date is missing from either column it's pulling from, it gives me a value error. The columns it's pulling from are formulas and do have value errors if one or the other is missing, so I tried removing those value errors to be blank and it seems to skewer the results of your formula. If that makes sense.

Thanks!!

 

[Marcelo Branco]

Try

=SUMPRODUCT(--(A3:A53>H3:H53),--ISNUMBER(A3:A53))

M.

 

[halloyd]

Thank you. I'll try that, but I wanted to also give you a sheet from the workbook so you have more resources available.
https://www.dropbox.com/s/vrk7b1n21a2pkc9/MrExcel Example.xlsx?dl=0

I'll report back!

 

[Marcelo Branco]

To compare Due Date with Today maybe something like this

=SUMPRODUCT(--(H3:H53>TODAY()),--ISNUMBER(H3:H53))

M.

 

[halloyd]

Try

=SUMPRODUCT(--(A3:A53>H3:H53),--ISNUMBER(A3:A53))

M.

Assuming this is for the value error problem, it is still displaying #VALUE .

 

[Marcelo Branco]

Assuming this is for the value error problem, it is still displaying #VALUE .

What you mean by "value error problem"? Are there cells that contain errors?
Please, be specific. An example would be helpful.

M.

 

[halloyd]

Also, I would like to extend this down to around line 300 so that if that many lines are added they are included in the formula. However, if a date is missing from either column it's pulling from, it gives me a value error. The columns it's pulling from are formulas and do have value errors if one or the other is missing, so I tried removing those value errors to be blank and it seems to skewer the results of your formula. If that makes sense.

Thanks!!

This is what I am referring to. It's in the dropbox example, but I'll show you:

The highlighted cell corresponds to the highlighted formula. The N/A in column G is causing the #VALUE in column H.

The red arrow shows what formula is in column H's #VALUE cell.

I appreciate your responsiveness!

 

[Marcelo Branco]

You need an array formula
=SUM(IF(ISNUMBER(A3:A53),IF(ISNUMBER(H3:H53),IF(A3:A53>H3:H53,1))))
confirmed with Ctrl+Shift+Enter, not just Enter

M.