# Stuck - help with function (amateur level)

#### Camel123

##### Board Regular
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.

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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?

Try

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

Or if the digits could appear anywhere, then maybe

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

1) it can be more
2) yes

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

"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.

Last edited:
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.

Maybe try the last one that I posted!

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

Excel 2013/2016
ABCDE
177 Fishing Rod42Function 1266
299 Fishing Rod87Function 256
321 bowling ball15
488 Fishing Rod64
599 Fishing net73
615 Fishing Rod41
Sheet1
Cell Formulas
RangeFormula
E1=SUM(SUMIFS(B:B,A:A,{"77","88","99"}&"*"))
E2=SUM(B:B)-E1

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.

Replies
9
Views
591
Replies
8
Views
355
Replies
7
Views
376
Replies
4
Views
510
Replies
1
Views
296

1,202,991
Messages
6,052,965
Members
444,622
Latest member
Kriszilla

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back