[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
Medium Priority
61 Views
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
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
• 2
• 2
• 2

LVL 53

Expert Comment

ID: 41859648
you can try formula:
``````=(SUMPRODUCT(--(TEXT(Sales[Date],"mmmm yy")=E\$2),Sales[Total]))
``````

and make sure E\$2 returns something like: October 16 instead.
0

Author Comment

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
0

LVL 53

Expert Comment

ID: 41859671
what if you trying this instead?
``````=(SUMPRODUCT(--(TEXT(Example[Date],"mmmm yy")=F\$1),Example[Total]))
``````
you can also attached a sample here so that we can diagnose the issue for you.
0

LVL 33

Accepted Solution

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

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

Author Comment

ID: 41860986
You did it, Subodh Tiwari (Neeraj)! Thank you!!
0

LVL 33

Expert Comment

ID: 41861066
You're welcome Steve! Glad to help.
0

## Featured Post

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…
###### Suggested Courses
Course of the Month12 days, 23 hours left to enroll