[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

EXCEL - Sumproduct using AND statement?

Posted on 2016-10-25
6
Medium Priority
?
61 Views
Last Modified: 2016-10-26
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!
0
Comment
Question by:steve bartels
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41859648
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
 

Author Comment

by:steve bartels
ID: 41859661
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
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41859671
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41859707
Try this.....

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

Open in new window

0
 

Author Comment

by:steve bartels
ID: 41860986
You did it, Subodh Tiwari (Neeraj)! Thank you!!
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41861066
You're welcome Steve! Glad to help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question