Excel formula - sumproduct

VirtualInsanity - Jul 8, 2012 at 05:43 PM
 VirtualInsanity - Jul 9, 2012 at 06:51 PM
Hi there,

I have a formula that I pieced together, which I thought would work but unfortunately it doesn't and I can't figure out why. The need the formula to check for 3 different things. It needs to check column K for the presence of the letter "C", check column M for dates that match the date in reference cell E2 (month/year) and check column P for the word "New". I need it to return a number result of how many records match that criteria. Here's the formula I have so far...

=SUMPRODUCT(('In Processing'!K$2:K$5000="C")*(TEXT('In Processing'!M$2:M$5000,"mm/yy")=TEXT(E2,"mm/yy"))*('In Processing'!P$2:P$5000="New"))

Any help is sincerely appreciated!

1 reply

Jul 9, 2012 at 06:51 PM
Upon closer inspection it occured to me that the actual problem is that the formula's weren't automatically updating when the reference cell E2 was changed. I did a quick look online for the source of that issue and found it's under Tools-Options-Calculations and yep sure enough for some silly reason it was set to Manual! Changed it to automatic and hey presto! Cells formulas update properly so the original formula was right after all. Problem solved. Sorry for jumping the gun. Will check things more thoroughly next time (will probably help if I don't have a head cold to contend with as well!). Thanks anyways :)