# Excel Question
**URL:** https://boards.straightdope.com/t/excel-question/610169
**Category:** Factual Questions
**Created:** [January 20, 2012, 8:07pm UTC](https://boards.straightdope.com/t/excel-question/610169 "2012-01-20T20:07:48Z")
**Posts on this page:** 18
**Page:** 1
### Author:  [@StGermain](https://boards.straightdope.com/u/StGermain)
#### Post date: [January 20, 2012, 8:07pm UTC](https://boards.straightdope.com/t/excel-question/610169/1 "2012-01-20T20:07:48Z")
Here’s a question for the Excel gurus out there. If’ I’m doing a SumIf formula, I can’t get it to add ranges. It only adds the first column in the range.
```auto
Product Q1 Q2 Q3 Q4
Cheese 25 25 25 75 =SUMIF(A:A, "Cheese",B:E ) 35
Bacon 100 100 100 200
Cheese 10 10 10 5
```
What I’m looking for is the sum of all for quarters for the criteria of Cheese, but it doesn’t work. If I use =sum and use a range, it’ll add everything up, but not with the criteria in place.
Does anyone have any ideas?
StG
---
### Author:  [@Munch](https://boards.straightdope.com/u/Munch)
#### Post date: [January 20, 2012, 8:48pm UTC](https://boards.straightdope.com/t/excel-question/610169/2 "2012-01-20T20:48:50Z")
Looks like SUMIF only adds one column. Why not add a Total column in F, and do =SUMIF(A:A, “Cheese”, F:F)
---
### Author:  [@StGermain](https://boards.straightdope.com/u/StGermain)
#### Post date: [January 20, 2012, 8:58pm UTC](https://boards.straightdope.com/t/excel-question/610169/3 "2012-01-20T20:58:08Z")
**Munch** - Because it ticks me off that the formula asks for a range and then doesn’t deliver. I thought I was doing something wrong. It may have to be the way I go, though. I’m setting this up for a group of users and hoped that all I’d have to give them is a formula.
StG
---
### Author:  [@Munch](https://boards.straightdope.com/u/Munch)
#### Post date: [January 20, 2012, 9:02pm UTC](https://boards.straightdope.com/t/excel-question/610169/4 "2012-01-20T21:02:23Z")
> [@StGermain](#):
>
> **Munch** - Because it ticks me off that the formula asks for a range and then doesn’t deliver. I thought I was doing something wrong. It may have to be the way I go, though. I’m setting this up for a group of users and hoped that all I’d have to give them is a formula.
>
> StG
Well, you can still do it, it just won’t be as easy to adapt to if there are additional quarters being added.
=SUMIF(A:A, “Cheese”,B:B)+SUMIF(A:A, “Cheese”,C:C)+SUMIF(A:A, “Cheese”,D:D)+SUMIF(A:A, “Cheese”,E:E)
---
### Author:  [@Kinthalis](https://boards.straightdope.com/u/Kinthalis)
#### Post date: [January 20, 2012, 9:13pm UTC](https://boards.straightdope.com/t/excel-question/610169/5 "2012-01-20T21:13:47Z")
I don’t understand why you are using SUMIF in this situation. Sum is what you want to give you a total column.
SUMIF is supposed to give you the sum of a key/data column pair based on a third column with a set of indexes/keys.
---
### Author:  [@FasterThanMeerkats](https://boards.straightdope.com/u/FasterThanMeerkats)
#### Post date: [January 20, 2012, 9:24pm UTC](https://boards.straightdope.com/t/excel-question/610169/6 "2012-01-20T21:24:54Z")
Clicking on the help feature, it says the range and sum\_range must have the same dimensions. E.g. if you want it to sum components in 3 columns then your condition range must look at 3 columns and each column must have the condition you want to check.
Sumif’s very powerful and easily my favorite function for querying data. Using columns with concatenated strings, it can give you pivottable-like outputs without the hassle of a pivot table, but yes, it has it’s limitations.
condition condition condition sum sum sum
range range range range range range
A A B\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_1 1 1
A A B\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_1 1 1
A A B\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_1 1 1
A A B\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_1 1 1
sumif A’s = 8
---
### Author:  [@StGermain](https://boards.straightdope.com/u/StGermain)
#### Post date: [January 20, 2012, 9:26pm UTC](https://boards.straightdope.com/t/excel-question/610169/7 "2012-01-20T21:26:42Z")
**Munch** - I hadn’t thought of that - thanks!
**Kinthalis** - How else would you add a range for criteria? Based on my sample, if you wanted to be aboe to just see your annual sales for cheese, how would you do it. In my real-life situation, there can be thousands of rows and about 8 colums to total. I don’t need the total of all items, just certain ones.
StG
---
### Author:  [@Munch](https://boards.straightdope.com/u/Munch)
#### Post date: [January 20, 2012, 9:26pm UTC](https://boards.straightdope.com/t/excel-question/610169/8 "2012-01-20T21:26:47Z")
> [@Kinthalis](#):
>
> I don’t understand why you are using SUMIF in this situation. Sum is what you want to give you a total column.
Because he wants the sum total of multiple rows (i.e. Rows 2 and 4, in this case), not just one.
---
### Author:  [@Munch](https://boards.straightdope.com/u/Munch)
#### Post date: [January 20, 2012, 9:32pm UTC](https://boards.straightdope.com/t/excel-question/610169/9 "2012-01-20T21:32:16Z")
> [@StGermain](#):
>
> **Munch** - I hadn’t thought of that - thanks!
>
> **Kinthalis** - How else would you add a range for criteria? Based on my sample, if you wanted to be aboe to just see your annual sales for cheese, how would you do it. In my real-life situation, there can be thousands of rows and about 8 colums to total. I don’t need the total of all items, just certain ones.
>
> StG
Well, depending on why you have two rows for Cheese, I’d probably have done it this way:
```auto
QUARTER STORE FOOD AMOUNT
Q1 1 Cheese 25
Q2 1 Cheese 25
Q3 1 Cheese 25
Q4 1 Cheese 25
Q1 2 Bacon 100
Q2 2 Bacon 100
Q3 2 Bacon 100
Q4 2 Bacon 100
Q1 3 Cheese 25
Q2 3 Cheese 25
Q3 3 Cheese 25
Q4 3 Cheese 25
```
Then you can pivot the hell out it, SUMIF it, pretty much anything you want to it, all day long.
---
### Author:  [@FasterThanMeerkats](https://boards.straightdope.com/u/FasterThanMeerkats)
#### Post date: [January 20, 2012, 9:52pm UTC](https://boards.straightdope.com/t/excel-question/610169/10 "2012-01-20T21:52:18Z")
> [@Munch](#):
>
> Then you can pivot the hell out it…
Sadly, this amuses my nerddom.
---
### Author:  [@StGermain](https://boards.straightdope.com/u/StGermain)
#### Post date: [January 20, 2012, 9:58pm UTC](https://boards.straightdope.com/t/excel-question/610169/11 "2012-01-20T21:58:18Z")
**Munch** - The actual spreadsheet had a list of transactions, trans types, balances, and aging buckets. So you have invoice 1234 for $150 which is 30 days past due. And credit memo 4321 for -$90 which is 150 days old, etc. Thousands of transactions. The users have to balance each trans type and then import the spreadsheet to Access. Only they have trouble making sure everything balances. So my plan was to make formulas for each group of transactions (or combinations of groups) and sum the balance column and the aging group columns. That way they can make sure they’re balanced before they import to Access.
It’s been a sort of tempest in a teapot here that out of maybe 400 reports imported per month, maybe 5 aren’t balancing. I’m trying to make it foolproof. Which isn’t easy, seeing the folks I work with.
StG
---
### Author:  [@amarone](https://boards.straightdope.com/u/amarone)
#### Post date: [January 21, 2012, 2:33pm UTC](https://boards.straightdope.com/t/excel-question/610169/12 "2012-01-21T14:33:07Z")
Type the formula =SUM(IF(A:A=“cheese”,B:E)) and then hold down Ctrl and Shift while you press the Enter key.
---
### Author:  [@jjimm](https://boards.straightdope.com/u/jjimm)
#### Post date: [January 21, 2012, 3:06pm UTC](https://boards.straightdope.com/t/excel-question/610169/13 "2012-01-21T15:06:17Z")
> [@Munch](#):
>
> Then you can pivot the hell out it, SUMIF it, pretty much anything you want to it, all day long.
Dude, you can pivot the ever-loving fuck out of the original table. You can pivot that motherfucker till the cows come home and demand that you pivot the fuck out of them too!
Er, I mean, OP, use a pivot table, they’re very easy to use in Office 2007 and up.
ETA: though **amarone** ’s solution is very elegant.
---
### Author:  [@StGermain](https://boards.straightdope.com/u/StGermain)
#### Post date: [January 21, 2012, 5:04pm UTC](https://boards.straightdope.com/t/excel-question/610169/14 "2012-01-21T17:04:16Z")
**amorone** -When I apply that formula to my example above, it gives me the total of B:E, not taking out the non-cheese item. So it gives me 685, when the total should be 185.
StG
---
### Author:  [@John\_Mace](https://boards.straightdope.com/u/John_Mace)
#### Post date: [January 21, 2012, 5:13pm UTC](https://boards.straightdope.com/t/excel-question/610169/15 "2012-01-21T17:13:13Z")
> [@amarone](#):
>
> Type the formula =SUM(IF(A:A=“cheese”,B:E)) and then hold down Ctrl and Shift while you press the Enter key.
What does holding down the Ctrl and Shift keys do?
---
### Author:  [@amarone](https://boards.straightdope.com/u/amarone)
#### Post date: [January 21, 2012, 5:14pm UTC](https://boards.straightdope.com/t/excel-question/610169/16 "2012-01-21T17:14:44Z")
> [@StGermain](#):
>
> **amorone** -When I apply that formula to my example above, it gives me the total of B:E, not taking out the non-cheese item. So it gives me 685, when the total should be 185.
>
> StG
It works for me. Did you do the Ctrl-Shift-Enter? If you do that properly, the formula will look as follows:
{=SUM(IF(A:A=“cheese”,B:E))}
Note the curly brackets.
Without the Ctrl-Shift-Enter, it will indeed return 685.
---
### Author:  [@amarone](https://boards.straightdope.com/u/amarone)
#### Post date: [January 21, 2012, 5:20pm UTC](https://boards.straightdope.com/t/excel-question/610169/17 "2012-01-21T17:20:26Z")
> [@John\_Mace](#):
>
> What does holding down the Ctrl and Shift keys do?
It makes the formula apply to an array. They are called array formulas or CSE formulas (CSE = Ctrl Shift Enter). The operation is then performed against each element in the array. Basically, you can write one formula instead of having to repeat it for row after row (or column after column).
See [here](http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx).
---
### Author:  [@StGermain](https://boards.straightdope.com/u/StGermain)
#### Post date: [January 21, 2012, 5:44pm UTC](https://boards.straightdope.com/t/excel-question/610169/18 "2012-01-21T17:44:33Z")
**amarone** - You’re right! I forgot to hold down the CtrlShiftEnter. That’s cool! I had no idea it would do that.
StG