Formula Change of month from Dec to Jan

[NeoDasari]

Hi,

I have a formula that is working fine, but when the month is changed from Dec to jan even though it doesn't have values in Jan, it is pulling a value? Do i need to modify at the DATEVALUE when moving from Dec to Jan?

=IFERROR((SUMPRODUCT(($A$2:$A$147=$F$4)*($C$2:$C$147="Yes")*(MONTH($D$2:$D$147)=MONTH(DATEVALUE(L1&"1"))))/$T$4),"")

[Kaper]

It shall probably be taken into account in last part of your sumproduct. Unfortunately without sample file it is not that obvious how to achieve this.

[Pete_UK]

It's not clear what you have in those columns, or what is in cell L1. If you are just adding one day to L1 then you don't need MONTH(DATEVALUE(L1&"1")) - you can just do:

MONTH(L1+1)

If you want to compare the month and year, then it might be better to do this:

...*(TEXT($D$2:$D$147,"mmyy")=TEXT(L1+1,"mmyy"))...

Hope this helps.

Pete

[NeoDasari]

Participation.xlsx

Attached is the Sample File.


Thank you,

[JohnTopley]

Blank cells result in data e.g. 01/01/2001 and you get a count of 2, giving 3% as result.

[NeoDasari]

Thank you.. resolved my issue.

Thanks everyone for your help.