Using Mid formular to check through a range of cells, then summing the figures.

[melving]

Hi people,

Currently i'm creating a outstanding payment list on excel that helps to consolidate figures and numbers that are overdue more than a year (Cell C44).

snip1.PNG

The issue with the image is that, the invoice #55 dated amounting to $55 on 12.12.17 is not yet a year outstanding but it appeared in Cell C44.

I format my date columns in dd.mm.yy as Text. So how I do i code it in such a way that, it checks all the date (Column B) and sums it into Cell C44 ONLY if it the date is outstanding greater than a year?

Here is the code sample.

Please Login or Register  to view this content.

[melving]

I tried using the MID formular (to extract the year from a range of cells) to integrate into the SUMIF formular but it didnt seem to work.

It only returned 0 as shown in the picture. Any idea why?


snip2.PNG

Here is the code for your reference.

Please Login or Register  to view this content.

[JeteMc]

Would it be feasible to put the date in as an actual date and use custom cell formatting 'dd.mm.yy'? If that is feasible the formula used in C44 should be simple. I have attached a rather generic example of how this would work. To get more specific help I suggest uploading the sample spreadsheet from which the screenshots in posts #1 and #2 were made. To upload a spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.