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!

 

[jtakw]

I think I should have responded in post 7 to ask OP if CUCxx was a cell reference or not.
Again, I might have just misunderstood OP.

 

[isafloor]

Oh I see, Excel thinks CUC18 is a cell, but it is actually a text. But it didn't work putting the CUC18 in " ", or separating with"," or with ";". I will give up and just use the array formula. I liked to be able to write the name of the articles, because I don't have to be referencing another table that might change.

 

[jtakw]

No, Excel wouldn't think CUC18 is a cell reference if the formula syntax is correct.
I thought CUC18 was a cell reference, it was my oversight not to have clarified with you regarding same.

So the formula Should work, in the US version of Excel, the syntax would be:

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

But since you're using a Spanish version of Excel, I don't know what needs to be changed,
Fluff gave a suggestion in Post # 13, I assume you've tried it.

I've checked with an online Excel Formula Translator, and it came up with the same as Fluff's suggestion.

Translator

The online tool for the translation of Excel formulas and functions from one language to another. Supports the replacement of argument separators, 35 languages and much more.

en.excel-translator.de

 

[jasonb75]

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

but unless "CUC18","CUC24","CUC30","CUC36","CUC42" are actual Text Values as the SUMIF criteria, it is not a Range of cells reference...and it won't work inside a single SUMIF

Although this is not the correct answer for the thread, a little trick you may not be aware of for using an array of single cells as criteria.

=SUM(SUMIF(venta!B:B,CHOOSE({1,2,3,4,5},CUC18,CUC24,CUC30,CUC36,CUC42),venta!H:H))

Or with dynamic arrays, I believe this should work.

=SUM(SUMIF(venta!B:B,INDEX(CUC:CUC,SEQUENCE(5,,18,6)),venta!H:H))

 

[isafloor]

No, Excel wouldn't think CUC18 is a cell reference if the formula syntax is correct.
I thought CUC18 was a cell reference, it was my oversight not to have clarified with you regarding same.

So the formula Should work, in the US version of Excel, the syntax would be:

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

But since you're using a Spanish version of Excel, I don't know what needs to be changed,
Fluff gave a suggestion in Post # 13, I assume you've tried it.

I've checked with an online Excel Formula Translator, and it came up with the same as Fluff's suggestion.

Translator

The online tool for the translation of Excel formulas and functions from one language to another. Supports the replacement of argument separators, 35 languages and much more.

en.excel-translator.de

I am now unable to do anything as my Excel is crashing with too many formulas

But, as last I tested, this is the same way you write it in Spanish and it didn't work. In the end the array formula is the only one I could get to work.

Thanks everybody for the help.

 

[jtakw]

Sorry couldn't help you further, thanks for the feedback.
I wonder if, you using an older version of Excel (2007) has anything to do with it.

 

[isafloor]

With the crash or with the formula not working? Because if you think the crash will improve I will look into updating.

 

[jasonb75]

I can't see any reason for the formula not working, or for one version to cause excel to crash, but not the other.

If you have used entire columns with a lot of criteria then that could be 1 reason for it crashing, especially if you have such formulas in multiple cells. Entire columns with SUMIF formulas are ok if used sparingly, but they waste a lot of system resources if you have too many of them, if that is the case then it is better to use dynamic ranges / structured tables.