Stuck - help with function (amateur level)

[Camel123]

Hi,

Column A: article numbers + products name, for instance "77 Fishing Rod"
Column B: sales per product

Function 1: If text strings in column A contain 77, 88 or 99. Then sum the sales for those article numbers/products only.

Function 2: Sum all sales for products that do not have article number 77, 88 and 99 in column A.

Appreciate all feedback, thanks.

 

[Camel123]

So, another question , if I want to sum all products NOT equal to article no 77, 88 and 99? I tried =SUM(SUMIFS(B:B,A:A,<>({"77","88","99"}&" *"))) but does not seem to work?

 

[FormR]

I tried =SUM(SUMIFS(B:B,A:A,<>({"77","88","99"}&" *"))) but does not seem to work?

Hi, no - it won't because when used like that the condtions are OR's rather than AND's.

You could do something like this:

=SUMIFS(B:B,A:A,"<>77 *",A:A,"<>88 *",A:A,"<>99 *")

Or simply sum the whole column and minus the result of the first formula.

 

[Camel123]

I'll stick with the simple solution but still want to learn the more complicated way, thank you a lot for taking time!