EXCEL - Sumproduct using AND statement?

First post, let's get to it!

I use a formula like this in my sheet -

=(SUMPRODUCT(--(TEXT(Sales[Date],"mmmm")=E$2),Sales[Total]))


This grabs a number if the value matches my columns month.

My problem is last years data is being added in again for last october, so I need a way to make it check for month and year.

I have many sheets, each for a different stage of business. Lets say I sold a product, I store that sale as 10/25/2016. Then, I have a summary sheet, which uses the sumproduct to retrieve this data later.

How can I change the formula up top to reflect this? I could add the current year somewhere in an open cell.

Open to ideas if there's an easier way using a totally different formula too! Thanks!
steve bartelsAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this.....

=SUMPRODUCT((TEXT(Example[Date],"yyyymmmm")=E$1&F$1)*Example[Cost])

Open in new window

0
 
Ryan ChongCommented:
you can try formula:
=(SUMPRODUCT(--(TEXT(Sales[Date],"mmmm yy")=E$2),Sales[Total]))

Open in new window


and make sure E$2 returns something like: October 16 instead.
0
 
steve bartelsAuthor Commented:
It doesn't work. I'm not sure how you intended to format F2. "November 16". Reformats to Custom "16-Nov"

 Here's a screenshot
ss--2016-10-25-at-08.40.59-.png
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ryan ChongCommented:
what if you trying this instead?
=(SUMPRODUCT(--(TEXT(Example[Date],"mmmm yy")=F$1),Example[Total]))

Open in new window

you can also attached a sample here so that we can diagnose the issue for you.
0
 
steve bartelsAuthor Commented:
You did it, Subodh Tiwari (Neeraj)! Thank you!!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Steve! Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.