Formula conversion from date string to numbers

Hello everyone,

The below formula works fine for what I want to do:

Please Login or Register  to view this content.

The problem with my formula is that it searches for the month and year as a string “*December**2017*” etc. as the date is entered as 8th December 2017. This is fine but I need to input the date as a number now 08/12/2017 and not a string. I tried to update the formula myself using sum product but was not able to get it working. Is there any way that my formula can be changed so that it checks for the month and year as numbers and not a string? So instead of the date being input as lets say 8th December 2017 it will be put in as 08/12/2017 and the formula will work as it does now?

My page has a lot of formulas set up like this that need to be changed but I just need to know how to do it on one so I change all of the others.

Thank you in advance for any help.

Regards,

Margate : )

I'd be inclined to use SUMPRODUCT rather than SUMIFS. You can then easily use the TEXT function to extract the month name and year.

That said, it looks, to me, as though the IF and repeated formula is redundant. You're saying, if the SUMIFS value is zero, return 0 but, if it's not, return the SUMIFS value. Either way, you return the value of the SUMIFS.

However, it would be easier, I think, to use the YEAR and MONTH functions to return the numeric values of the year and month and then amend your formula, or the alternative SUMPRODUCT formula, to use those numeric values instead.

Replace the " around the word accountancy with the standard " (same key as digit 2 on a UK keyboard....)

can you attach your workbook? I got "0" using an empty sheet called accountancy

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

There was some redundant text in your formula, but the main problem was that the last term was referring to column K. It should have been column N:
=SUMPRODUCT((MONTH(Accounts!E3:E4)=12)*(YEAR(Accounts!E3:E4)=2017)*(Accounts!K3:K4="Accountancy")*(Accounts!N3:N4))

We all make mistakes... It's how we learn. Thanks for the rep.

You're welcome. Thanks for the rep.


To be fair, Glenn did all the work ... but thanks again, anyway