See Sum with multiple criteria. Same question has been asked many times. Use the search to locate them
Hi everyone,
I am somewhat of an Excel Novice.
My goal is to use the SUMIF formula, but expand it to use two 'range' and 'criteria' within it's parameters to SUM just one adjacent 'sum_range'.
I thought this would be easy enough, by just nesting AND into the SUMIF, but no matter how many times I try to nest various combinations of formulas with IF and VLOOKUP and/or various other failed attempts, I believe my syntax and quite possibly my approach is incorrect. I won't post my attempts here, as they are probably quite embarrassing.
Please help, I would greatly appreciate it. This is only my 2nd day with Excel 2003.
Thanks in advance,
Nick
Last edited by BlindMelon; November 23rd, 2006 at 18:12.
See Sum with multiple criteria. Same question has been asked many times. Use the search to locate them
Welcome to Ozgrid. There are several ways to do this. I suggest you look into the sumproduct funtion and then post back with more info if you still stuggle.
Search this site on sumproduct beforehand if required.
Sorry about posting this thread, I realize it's been overposted in the past. I still don't really get SUMPRODUCT. I do know that I finally got it to work, but I'm not sure why it works. I posted a sample of my spreadsheet I'm attempting to complete. The actual spreadsheet is much larger. As you can see, it consists of many Bank Check entries in a series. I need to create a table that shows trends - possibly with a line graph of expenditures by Type/Description over the course of years between 1995-2006. So I need to total the Check Amount Column cells that correspond to the Type/Description cells and also the Date cells. In order to create a chart that shows a nice timeline.
To give some further clarification; I am going to create another Sheet within my workbook that will pull in data using this Formula I'm trying to figure out here - and will show Description/Type on the left hand side of the axis and then full Years on the top. I will have Totals within each data cell that correspond to the Description & Year. Then I could make a PivotTable? I tried to make a PivotTable with the current Sheet's Range and it won't give me anything that looks good because I have so many entries for so many dates within years. (and a lot of years...) I guess I give a poor explanation, but I hope someone understands me.
As you can see in the spreadsheet on the far right column, I've attempted to get the SUMPRODUCT formula to work based on various people's advice or examples. I finally got the SUM of 125 to come up, which is accurate. I nested YEAR inside of SUMPRODUCT and it finally worked. But as you can see up top in that column, I omitted YEAR( and it did not work... Or it gives some crazy numbers and then #VALUE, #NUM, etcetera. If anyone wants to shed some light on my problems, I would be most grateful.
These are the Formulas I tried and the results I got (or you can see these in the spreadsheet as well) (I was holding down Shift+Ctrl+Enter to get Curly Braces to denote an Array Formula?):
Formula:
=SUMPRODUCT((G2:G32=1996)*(F2:F32="Horse Related Expense"),C2:C32)
Result:
105507 (Why?! This is wrong.)
Formula:
=SUMPRODUCT(($G$2:$G$32=1996)*($F$2:$F$32="Horse Related Expense"),$C$2:$C$32)
Result:
105507 (Why?! This is wrong.)
Formula:
=SUMPRODUCT(IF(G2:G32=1996,1,0),IF(F2:F32="Horse Related Expense",1,0),C2:C32)
Result:
#VALUE! (?)
Formula:
=SUMPRODUCT((Year=1996)*(Desc="Horse Related Expense"),Check_Amount)
Result:
#NUM! (?)
=SUMPRODUCT((YEAR(C2:C32)=1996)*(F2:F32="Horse Related Expense"),D2:D32)
Result:
125 (This is correct, but just because I use YEAR() to calculate?)
I don't get it, but I will resume my attempts tomorrow. I'm sure it's not really that complicated, is it? Stop laughing at me .
Thanks for the help!
Nick
Last edited by Dave Hawley; November 24th, 2006 at 09:08.
Firstly you need not need to enter sumproduct with ctrl + shift + enter. even through it works with array formulas you do not need to enter it in the same manner you would an array formula.
=SUMPRODUCT((G2:G32=1996)*(F2:F32="Horse Related Expense"),C2:C32)
returns 105507 because you are summing the date column and dates are serail numbers such as 35112,35194,35201. Change column c to d and you get the result required
Your next example exactly the same.
The rest of your formulas are in the wrong syntax to return a result.
Where you are using named ranges you are refering to the whole column. Sumproduct will not function this way.
Last edited by Dave Hawley; November 24th, 2006 at 09:08.
Thanks Badger, that really clears things up for me. Named Ranges would work if they weren't the entire column. And yes, I used the wrong range... I was tired or something.
But why does SUMPRODUCT work this way? From reading about the Formula in Excel Help it appears to multiply components within corresponding arrays and then add them together...?
It's not SUMIF, but that's the purpose I'm using it for...
=SUMPRODUCT((G2:G32=1996)*(F2:F32="Horse Related Expense"),C2:C32)
(G2:G32=1996) should actually return TRUE or FALSE? It's an IF conditional statement? It's not an assignment... So within the range of G2:G32, it's going to go from TRUE to FALSE depending on which cell meets the condition of 1996? But why does it multiply it with the next operation?
I understand that this works, but I don't understand why it works. According to Excel Help; SUMPRODUCT is just more general solution for doing SUM multiplication operations.
I don't understand why the * operator is being used... It's arithmetic not logical... It bothers me that I don't understand this. In fact there is nothing in the Help that says that this syntax works... It says quite simply, "Multiplies corresponding components in the given arrays, and returns the sum of those products." I notice that you're basically doing: =SUMPRODUCT((Range=Condition)*(Range=Condition),Sum_Range). Hmmmn.
Maybe someone could shed some light.
Thanks a lot Badger101 for your previous post, it helped a lot.
Last edited by Dave Hawley; November 24th, 2006 at 09:09.
Hi,
the reason the SUMPRODUCT works is known as boolean maths
if a logical condition is true the test returns a 1
if a logical condition is false it returns a 0
if you multiply a value by 1 you get the value you started with
if you multiply anything by 0 you get 0
therefore it adds the values where your conditions are true
=1*1*1000
if any of your conditions are false the result is 0
=1*0*100000
essentially imagine that each argument is an IF statement and each * is an AND statement with a sum at the end, pretty much as you noted at the end of your last post.
This is performed on every row in the ranges selected and returns the total of the values where all the arguments hold true since for each row where any argument is false it will return a zero
hope this helps you make sense of this, it took me a couple of hours to figure it out the first time I encountered it.
Last edited by tsuimark; November 25th, 2006 at 05:43.
Thanks tsuimark,
I do now completely understand why SUMPRODUCT works for this application. I didn't realize that TRUE was = 1 and FALSE = 0. When you describe it that way, I do understand. I thought any time you used boolean logic it returned "TRUE" as a string, not the value of 1. Sometimes it does, right? Or is TRUE always really just the value of 1 and FALSE the value of 0? I have a lot to learn. Thanks for your help.
BlindMelon, In Excel, TRUE equates to the value 1 and FALSE equates to the value 0, and they can be used interchangeably. If you enter TRUE in A1 and TRUE in B1, and the formula
=AND(A1,B1)
in C1, the result is TRUE. Change A1 to 1 and the result is still TRUE. To get the answer 1, use
=C1*1
Thanks ByTheCringe2. That does make sense now.
Do you understand using SUMPRODUCT?
It's supposed to be =SUMPRODUCT(Array_1, Array_2)
Takes the product of Array_1 and Array_2, then adds them together.
I'm using this Formula for a purpose it wasn't really designed for by combining it with boolean logic to return a value from an adjacent cell if that cell meets conditions from two other adjacent cells in other columns. Which are also within ranges of like size/shape.
So SUMPRODUCT((Range_1=Condition)*(Range_2=Condition),Sum_Range)
Where Sum_Range is the column of cells I want to have returned to the cell I'm typing this formula into, and Range_1 and Range_2 are ranges with cells adjacent to the one I'm trying to have return if certain conditions are met.
My question is:
Wouldn't this equate to SUMPRODUCT((1)*(1),Sum_Range) if it were TRUE, let's say Sum_Range is 25. Now it's SUMPRODUCT(1,25) or (1+25) after the formula does it's function/operation. Now it's 26?
It doesn't appear to work this way, but I don't understand why.
Also what if you wanted to return a String?
Also if I have a condition that is not a string, let's say within Array_1, of Range_1=1999, to find the year 1999. But within some cells I put a "?" because I didn't know the value - it returns #VALUE...? I think this is why. Maybe there is some IF conditional statments I could nest inside there as well to disregard "?" entries? - Actually it seems to not have a problem with "?" strings even though the condition is a numerical value. Hmmmn.
I attached a sample of the spreadsheet I am attempting to complete.
I have a very weak grasp of how to use PivotTables, but I need to create charts and graphs based on this data in this spreadsheet. My problem is that there are too many entries by dates to show on a chart, so I want to consolidate all the date entries into Years. Sum the total of all the entries by Description/Type, then by Year. Then I should have a chart that works! I can make a PivotTable that shows the Description/Type column on an Axis with a 'Sum of Check Amount', and that shows me sum totals by Description/Type, but I can't add in Years because I don't have the data listed in that fashion. I have Dates, not Years.
If anyone has a few moments to take to explain some simple things I'm missing here, I would be most grateful. Thanks for all your help so far as well.
Nick
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks