Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Question about Sumproduct Function

Posted on 2013-12-27
2
Medium Priority
?
499 Views
Last Modified: 2013-12-28
Hi Experts,

The function below attempts to use Trim function on a range name so that I can indent the referenced cell (the range has no indents). It fails as soon as I add a space to the name in A11.

In another case I tried to extract the year from a date by using the Year function in a similar way to what you see below. That also failed and I had to create a new column in the data.

=SUMPRODUCT(-(TRIM(tblSalariesName)=$A11),-(tblSalariesYear=B$3),(tblSalariesAmount))

I admit to being a Lotus 1-2-3 user when I can be wherein all of this is quite simple and elegant.

Thanks for any help and have a happy new year
0
Comment
Question by:rjfields
[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 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 39743005
You would need to apply TRIM to A11 too, try like this

=SUMPRODUCT(-(TRIM(tblSalariesName)=TRIM($A11)),-(tblSalariesYear=B$3),tblSalariesAmount)

Note that TRIM will remove leading and trailing zeroes and also reduce repeated consecutive internal spaces to a single space, so that might affect your comparison

If you don't want to have a separate column for the year you should be able to use this version

=SUMPRODUCT(-(TRIM(tblSalariesName)=TRIM($A11)),-(YEAR(tblSalariesDate)=B$3),tblSalariesAmount)

....but that will fail if tblSalariesDate includes any text values (e.g. a header) because YEAR function applied to text gives #VALUE! error, an alternative is to use TEXT function which will be OK with headers, i.e.

=SUMPRODUCT(-(TRIM(tblSalariesName)=TRIM($A11)),-(TEXT(tblSalariesDate,"yyyy")=B$3&""),tblSalariesAmount)

regards, barry

PS using SUMPRODUCT with a single - in front of each condition is valid but it's more normal to double those to --

If you use a single - you need to make sure you have an even number of conditions otherwise your result might be negated (e.g. you get -2000 instead of 2000)
0
 

Author Closing Comment

by:rjfields
ID: 39743155
Thank you very much.

It turns out I made a hugely lame mistake misplacing the Trim function. OY! I caught it looking at your example. Also, you are right about the unary operator. I had an even number of them otherwise one needs to use a double unary (--) operator.

12/27
I did try to cut and paste your suggestion for the Year function above after redefining the range to exclude the column header. I also shortened the range to 10 rows and verified that I had valid dates within it. I still get #value as a return complaining about a datatype error.

12/28

Indeed you were correct about the header in the range definition causing the #value error. I just needed to take the additional step of adjusting all the other relevant ranges to omit the header cell as well to preserve the symmetry of the query.

Again thanks much
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

715 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