Excel Question

Factual Questions
1

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.



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

2

Looks like SUMIF only adds one column. Why not add a Total column in F, and do =SUMIF(A:A, “Cheese”, F:F)

3

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

4

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)

5

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.

6

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

7

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

8

Because he wants the sum total of multiple rows (i.e. Rows 2 and 4, in this case), not just one.

9

Well, depending on why you have two rows for Cheese, I’d probably have done it this way:



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.

10

Sadly, this amuses my nerddom.

11

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

12

Type the formula =SUM(IF(A:A=“cheese”,B:E)) and then hold down Ctrl and Shift while you press the Enter key.

13

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.

14

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

15

What does holding down the Ctrl and Shift keys do?

16

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.

17

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.

18

amarone - You’re right! I forgot to hold down the CtrlShiftEnter. That’s cool! I had no idea it would do that.

StG