Summing a field in Excel using multiple criteria

[J.T.]

I need to create a formula for Excel that sums a field using 3 criteria...for example....

Column A Column B Column C
A 50 2
B 50 3
A 59 1
A 65 2

I need to sum column C for those that are in A and between 50 and 60 inclusive (the answer would be 3). Thanks for the help in advance.

[thing]

{=sum(if(b1:b5>=50,c1:c5))-sum(if(b1:b5>60,c1:c5))}

That's an array formula, instead of enter, press ctrl-shift-enter

[thing]

Oops, missed your third criterion. Or first. Whichever.

How's about:
Column D
=if(a1=A, if(and(b1>=50,b1<=60),c1,0), 0)

And then sum that column.

[J.T.]

In your first response, Thing, are you using a minus sign in between the two criteria? I had read to use a * symbol, but that doesn't seem to be working.

[J.T.]

Let me also say that I would like to have one formula, and not an extra column...I have about 6 different values in Column A and 6 or seven ranges for the Column B numbers. If I have to create a column for each, I will have to create about 42 columns. This is something that I have to do monthly.

[thing]

Yes, a minus sign. sum(if(b1:b5>=50,c1:c5)) by itself would've given you 8, subtracting sum(if(b1:b5>60,c1:c5)) takes out the extra 2. But it still ignore column A entirely.

[thing]

If you sort by column A, will the number of rows labeled A, B, etc., hold still from month to month?

[J.T.]

No...unfortunately it won't....

[Patience]

Let me also say that I would like to have one formula, and not an extra column...I have about 6 different values in Column A and 6 or seven ranges for the Column B numbers. If I have to create a column for each, I will have to create about 42 columns. This is something that I have to do monthly.

Can you set up 1 column that is a concatenation of column A & B giving you?

A...B....C.....D

A...50...2...A50
B...50...3...B50
A...59...1...A59
A...65...2...A65

then use the following

+SUMIF($C$1:$C$4,">=a50",$D$1:$D$4)-SUMIF($C$1:$C$4,">=a60",$D$1:$D$4)

I would think Sumif could do a range, but this should do it just a bit longer

[GadgetGeek]

Can you put it into a pivot table and then create groups for your Column B stuff? I realize that you would have to redo it monthly, but it shouldn't take too long to rebuild the pivot table.

[J.T.]

My formula actually worked, but I had N/A's for some of my column B stuff that was screwing it up....for those of you who are curious, here is the formula....

{=sum((ColumnA="A")*(ColumnB>=50)*(ColumnB<=60)*ColumnC)}


Thanks for all of the great ideas....

[Dr T Non-Fan]

Without the array fancy frenchy curly brackets, you can use sumproduct the same way: with parentheses around each condition.

SUMPRODUCT((C1:C4)*(A1:A4="A")*(B1:B4>=50)*(B1:B4<=60))

I find that the arrays, when conditionally summing thousands of rows, tend to be slower. you also can't break up the arrays. You have to "unarray" them then "rearray" them. SUMPRODUCT can be contracted or expanded. Great for monthly updating.

[J.T.]

DTNF: I was under the understanding that the SUMPRODUCT function didn't work for Boolean values. I tried your formula and couldn't get it to work. Am I doing something wrong?

[Patience]

I tried it also & it worked for me

[J.T.]

Thanks, DTNF. I got it to work. It was thinking that my numbers were text.

[Dr T Non-Fan]

I've done that before -- thought that text were numbers (well, they looked like numbers on the screen). I use this extensively for monthly reporting, and have for years. It breaks down (takes too long to recalc) at about 8000 rows of data. In that case, I keep the formula at the top, copy and paste the formula, recalc, then paste-special-values over themselves, keeping the master copy at the top.

A more fancy version (if it's a recurring project) for documentation purposes is to name the range, then use (INDEX(RANGENAME,,1)="A").
Works best when "rangename" is smaller than the sheet name. Two commas are required, as the second term is the row number, and not filling it defaults it to all.