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]

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.

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.

See Bold red underlined above.

Something like SUM(SUMIF(venta!B:B,CUC18,venta!H:H),SUMIF(venta!B:B,CUC24,venta!H:H),and so on.....

 

[isafloor]

Spanish.

 

[Fluff]

In that case try

=SUMA(SUMAR.SI(venta!B:B;{"CUC18";"CUC24";"CUC30";"CUC36";"CUC42"};venta!H:H))

 

[Fluff]

See Bold red underlined above.

See post#8, or post#2

 

[jtakw]

See post#8, or post#2

I know that Fluff, 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 with curly brackets. Again, unless these are actually Text Values as criteria.

 

[Fluff]

As the OP posted two formula one with range references & one with values in "" I took it that the CUC18 etc are text values located in cells A4:A8

 

[jtakw]

See Post #3, 5, and 6

I believe OP is trying to use Separate cell references (not in a range) which "houses" his sample criteria (Apple, Orange, Pear, etc.)
But, CUCxx, might be his Text criteria, if that's the case, it'll work.

I may be just misunderstanding OP.

 

[Fluff]

It's post#6 I was looking at where you have

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

using (I think) range references, which should be

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

and this

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

which I suspect is the same but now using text values, rather than ranges.
We'll have to wait & see what the OP says, but could certainly do with some clarification.

 

[jtakw]

Post #6 is by OP,
My 1st post in this thread is #7.
I agree, we'll need to see OP's response.

 

[Fluff]

I blame MS for giving us too many columns, back in the day CUC18 could only have been text.