SUMIF Using Dates as the Variable

[James8761]

Hi,

I am trying to do a credit control forecast in excel. I want to know two things.

On every day for the next 3 months how much money is due each day. That bit I am fine with and have a SUMIFS formula in which works.

The second part is; I also want to know how much is still due on old amounts. i.e. how much we should have collected but haven’t. So I want to know how much in total (just one figure) is due for the 29^th April 2018 and prior.

The forecast model I have built and data I am looking at are in different tabs.

The Due Dates are in Sheet1 Column C. The Date I am looking at (29^th April 2018) is in Cell B3 of my “Cashflow” tab. The Overdue Amounts are in Column I of Sheet1.

I had this formula in, but it gives the incorrect total =SUMIF('Sheet1’!$C:$C,">"&'Cashflow'!B3,'Sheet1'!$I:$I)

There are also amounts in Sheet1 Column I that are Subtotals for each individual account. These have no date in Column C and I want to avoid “Summing” these. Is this where the above formula is going wrong??

Thanks for any help.

 

[steve the fish]

That formula is going to sum the amounts in column I which have a date thats after the date in B3 in column C. Is it returning an amount? If so then more than likely you have dates that excel sees as text and not dates. You can test this with =ISNUMBER(date) down column C. You should also check B3.

 

[James8761]

That formula is going to sum the amounts in column I which have a date thats after the date in B3 in column C. Is it returning an amount? If so then more than likely you have dates that excel sees as text and not dates. You can test this with =ISNUMBER(date) down column C. You should also check B3.

Hi, many thanks for the reply. Yes I do get an amount with my current formula.

When I try ISNUMBER in both Column C and B3 i get the answer TRUE?

 

[James8761]

That formula is going to sum the amounts in column I which have a date thats after the date in B3 in column C. Is it returning an amount? If so then more than likely you have dates that excel sees as text and not dates. You can test this with =ISNUMBER(date) down column C. You should also check B3.

Oh i see your word "After" now.....I'm a wally. I've changed the arrow sign to < and it gives me the answer I want, Doh!! Thank you for making me think!

 

[steve the fish]

Every single answer is true in column C? Is the number higher than expected or lower?