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.

 

[gaz_chops]

Not sure where you are getting the one's you are trying.

You need to try either the one I posted last, or FormR's.

 

[Camel123]

First formula seem to work suggested by FormR! Thanks a lot. I just want to understand its logic because the "*" is what makes it return the specific value for the criterias met. Is it not a wild card and is there any risks that it will sum approximate matches?

 

[gaz_chops]

Yes its a wild card, it will match any cells that begin with the numbers you specified, followed by ANY other text.

 

[Camel123]

Yes its a wild card, it will match any cells that begin with the numbers you specified, followed by ANY other text.

Is there any work around to make it compute only exact matches and somehow replace the wildcard in the suggested formula by FormR? I tried to remove the wildcard and add isnumber/find but then it just turns to zero.

 

[gaz_chops]

Not sure what you mean?

My formula & FormR's, finds {77,88,99} if it matches it will sum the corresponding values in Col B.

 

[FormR]

I tried to remove the wildcard and add isnumber/find but then it just turns to zero.

I'm not sure what you mean either - the sumifs() option specifically checks that the values in column A start with 77, 88 or 99 - can you post some examples of possible column A entries that you think will result in a false positive?

 

[Camel123]

I tried yours but does not seem to work or I am doing something wrong, evaluation: =sumproduct((isnumber(({1;#VALUE!;#N/A}))*B1:B15)). Can you post a picture form your workbook like FormR did? FormR's formula works but I have some article numbers with the same beginning, for instance: 99XX, 99YYYY, 99Z and I can not risk the formula to sum all of those together due to the wildcard. Hope it made my point more clear

 

[FormR]

I have some article numbers with the same beginning, for instance: 99XX, 99YYYY, 99Z and I can not risk the formula to sum all of those together due to the wildcard.

Using a combination of ISNUMBER(FIND()) will not help resolve that, you could try changing the SUMIFS() to check that the column A values start with the two digit number followed by a space like this:

=SUM(SUMIFS(B:B,A:A,{"77","88","99"}&" *"))

 

[gaz_chops]

Try this, I tweaked FormR's

=SUM(SUMIFS(B:B,A:A,{"77","88","99"}&" *"))

 

[Camel123]

I have no space's in the article numbers so it seems to work perfect so there is no risk capturing the wrong products. You guys are best, appreciate your support a lot - thank you !!