Using sumif to add a series different criteria in the same column.

[isafloor]

Hello, I saw some similar posts but even though the problem is the same, I don't quite get them.

I have a series of products that I want to add together, and this criteria can change. For example:

Product	Sales period 1	Sales period 2
Apples	10	5
Pears		1
Celery	9	4
Pumpkin	5	2

I would like to add Apples, Pears and Celery, but not Pumpkin. This list has 4 items, but the real one has 50. I'd like to avoid doing a series of sum.if for each individual item, and instead doing something like:

=sum.if(A:A,"apples"or"pears"or"celery", B:B)

It would also be nice to have an option to reference another place that would be great to, for example supposing the list "apples / pears / celery / pumpkin" is in D1:D4:

=sum.if(A:A,D1:D3,B:B)

Are these two options possible somehow?

Thanks!

 

[jasonb75]

You need to use

=SUM(SUMIF(A:A,D1:D3,B:B))

Which must be array confirmed with Ctrl Shift Enter.

 

[isafloor]

Great, and is it possible to do it as the first example (manually introduce the names in a string of names)? Something like: =sum.if(A:A,"apples"or"pears"or"celery", B:B)

 

[isafloor]

And another question, each time I modify the formula I will have to enter ctrlshiftenter? Because I am using this formulated sheet to change the name of the excel file with find and replace, as I start getting more recent sales report I would substitute the name of the file in all formulas.

 

[jasonb75]

For that you would use an array constant.

=SUM(SUMIF(A:A,{"apples","pears","celery"},B:B))

This one does not need array confirming.

Regarding your follow up question, you shouldn't need to array confirm it again if you are using find and replace to edit the formula, but you would need to if you manually edit the formula in the cell.

 

[isafloor]

I can't get the one without array to work, I tried two options, and both won't even let me push "enter", I get an error popup:

SUM(SUM.IF(venta!B:B,(A4,A5,A6,A7,A8),venta!H:H))

SUM(SUM.IF(venta!B:B,("CUC18","CUC24","CUC30","CUC36","CUC42"),venta!H:H))

Any ideas? Column B is the name of the product, and H the sales.

Cheers,

 

[jtakw]

Hi,

You won't be able to use Cell references with the SUM(SUMIF formula, the values Must be "hardcoded" and within curly brackets, not parenthesis.

Edit: Unless you use separate SUMIF for Each Cell reference, then SUM.

 

[Fluff]

You can use a range for the criteria, like Jason did in post#2

 

[isafloor]

Thanks, I didn't notice the curly brackets. I changed it to:

SUM(SUM.IF(venta!B:B,{"CUC18","CUC24","CUC30","CUC36","CUC42"},venta!H:H))

It still won't let me hit "enter" I get the error still.

 

[Fluff]

What language is your version of Excel?