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.

 

[CyrusTheVirus]

Hey cameltoe (lol),

Questions:

1) do all of the products start with two digits? Or could there be less or more?
2) Are the digits within the product name always at the beginning of the name?

 

[gaz_chops]

Try

=SUMPRODUCT((LEFT(A1:A15,2)+0={77,88,99})*B1:B15)

 

[gaz_chops]

Or if the digits could appear anywhere, then maybe

=SUMPRODUCT((ISNUMBER(FIND({77,88,99},A1:A15)))*B1:B15)

 

[Camel123]

1) it can be more
2) yes

I tried both of the suggested formulas but none worked properly :/

 

[gaz_chops]

"none worked properly" doesn't really help! In what way did they not work? What result did they give? What result did you expect?

Post a small sample of data with expected results.

 

[Camel123]

I only had error. I tried the following function:

{=IF(ISNUMBER(FIND({77;88;99};A11:B27));SUM(B11:C27);"")}

The issue is it sums all the amounts in column B rather than only the cells where the criterias are met. I tried to use sumif within the if function but did not work out very well.

 

[gaz_chops]

Maybe try the last one that I posted!

 

[FormR]

Hi, here's another option that you can try:

Excel 2013/2016
	A	B	C	D	E
1	77 Fishing Rod	42		Function 1	266
2	99 Fishing Rod	87		Function 2	56
3	21 bowling ball	15
4	88 Fishing Rod	64
5	99 Fishing net	73
6	15 Fishing Rod	41
Sheet1

Cell Formulas
Range	Formula
E1	=SUM(SUMIFS(B:B,A:A,{"77","88","99"}&"*"))
E2	=SUM(B:B)-E1

 

[Camel123]

I get #value !. Doesn ot work. Also tried:

{=SUMIF(A11:A27;ISNUMBER(FIND({77;88;99};A11:A27));B11:B27)} but only get 0.

Evaluation shows {=SUMIF(A11:A27;TRUE));B11:B27)} but does not sum the amount for the criterias met.