Formula MalFunction

Hello Riz,

It works for me - if I change G2 to 18 then column P turns to zeroes as expected - do you have calculation set to automatic or manual?

btw, I think TEXT function in SUMIFS is redundant, it works OK as just

=SUMIFS($D:$D,$A:$A,P$3,$C:$C,$F6)

I'm not sure I can explain what is going on, but I think the error is somehow in the TEXT() function. Excel appears to be trying to interpret the text in row 3 as a date, then back to text in the desired format, which changes all of the mmm18 to mmm17. Test using Formula evaluate tool (https://support.office.com/en-us/art...6-A70AA409B8A7 ) and see that, when Excel evaluates the text formula "Oct18" is changed to "Oct17" (same for the other months as well, but they still evaluate to 0). You can also see this by putting =TEXT(G3,"mmmyy") into G4 and copying across.

I am not sure what solution to recommend. Since all of your monthyy values are simple text values and not date serial numbers, I might suggest that you get rid of the TEXT() function so that Excel will use the text in row 3 as is without trying to reinterpret the text as a date serial number (since it is not doing so in the way you expect) =SUMIFS($D:$D,$A:$A,P$3,$C:$C,$F6). The other thought might be to convert all of your mmmyy text strings (in both column A and row 3) into real date serial numbers and use them that way.

ETA after seeing dll's post -- is this behavior different across different versions? I see the same behavior as suggested by the OP in 2007.

Originally Posted by MrShorty

ETA after seeing dll's post -- is this behavior different across different versions? I see the same behavior as suggested by the OP in 2007.

I suspect it may be more to do with regional date settings - if you use mdy as default date setting then TEXT function might interpret OCT18 as 18th October in the current year, and then "mmmyy" changes that to OCT17, here in the UK beacuse we use dmy then OCT18 is always interpreted as 1st October 2018