"*" for text in SUMPRODUCT

[wallaly]

Hello!

I'm stuck with a problem, hope someone can help!

In column G of Sheet1 I have a list of text, within each cell there can be from 2 to 8 letters, containing many abbreviations divided by slash signs ("/") (i.e "CA/LF").
Column N is the number I want to add (quantity of products made).

In A1 of Sheet2 I have abbreviations of two letters for which I want to make the calculation (i.e "CA")
In Cell B1 of Sheet2 I have the following formula:

=SUMPRODUCT(--('Sheet1'!$G$2:$G$9682=($A1&"/"&"*"));'Sheet1'!$N$2:$N$9682)

For some reason the "*" part doesn't work and the formula always returns 0. Any ideas on how can I get this formula to work?

Please note that using "search" or "find" would consider "WCA/SD" in the calculation for the "CA" search.

Thank you in advance for any help!



P.S: The actual formula in order to check if the abbreviation is in any part of the cell is: "=SUMPRODUCT(--('Sheet1'!$G$2:$G$9682=($A1&"/"&"*"));'Sheet1'!$N$2:$N$9682)+SUMPRODUCT(--('Sheet1'!$G$2:$G$9682=("*"&"/"&$A1);'Sheet1'!$N$2:$N$9682)+SUMPRODUCT(--('Sheet1'!$G$2:$G$9682=($A1));'Sheet1'!$N$2:$N$9682)+SUMPRODUCT(--('Sheet1'!$G$2:$G$9682=("*"&"/"&$A1&"/"&"*"));'Sheet1'!$N$2:$N$9682)

 

[Jonmo1]

Welcome to the board.

Yep, wildcards don't work like that in sumproduct.
You can use the LEFT function though..

Try

=SUMPRODUCT(--(LEFT('Sheet1'!$G$2:$G$9682;3)=$A1&"/");'Sheet1'!$N$2:$N$9682)

 

[wallaly]

Jonmo1, Thank you very much for your welcome, help and quick answer .

I tried your formula, and it worked perfectly making a small adjustment to consider the 3 letter abbreviations and if the abbreviation is after the slash sign (i.e:searching "CA" in "CHD/CA"):

=SUMPRODUCT(--(LEFT('Sheet1'!$G$2:$G$9682;LEN($A1)+1)=$A1&"/");'Sheet1'!$N$2:$N$9682)+SUMPRODUCT(--(RIGHT('Sheet1'!$G$2:$G$9682;LEN($A1)+1)="/"&$A1);'Sheet1'!$N$2:$N$9682)

Now the problem i'm having is when the abbreviation is in between slash signs, for example searching "CA" in "CHS/CA/SD" or "SD/CA/CAD", or searching "CAD" in "CHS/CAD/DCA" because i'm unable to use the MID function.

The abbreviations are 3 letters maximum searched in a 11 letter string max (AAA/BBB/CCC).

Any ideas?

Thank you again for your help!

 

[Jonmo1]

How about

=SUMIF('Sheet1'!$G$2:$G$9682;"*"&$A1&"/*";'Sheet1'!$N$2:$N$9682)

 

[Redwolfx]

Pehaps I'm worng, but couldn't you use?

=SUMIF('Sheet1'!$G$2:$G$9682,"*"&$A1&"/*",'Sheet1'!$N$2:$N$9682)

Jonmo, you read my mind, and faster than I read it.

 

[wallaly]

Hello Redwolfx, thank you also for your help.

The reason of using SUMPRODUCT in that way instead of SUMIF is that the abbreviations are not the only criteria to include in the sum. Posting the question into the forum I simplified the formula hence deleting a criteria which is that the date has to be within a range.

The formula in my file is actually more like this:

=SUMPRODUCT(--(LEFT('Sheet1'!$G$2:$G$9682;LEN($A1)+1)=$A1&"/";--($C$1>='Sheet1'!$I$2:$I$9682);--(('Sheet1'!$I$2:$I$9682)>=$D$1)));'Sheet1'!$N$2:$N$9682)+SUMPRODUCT(--(RIGHT('Sheet1'!$G$2:$G$9682;LEN($A1)+1)="/"&$A1);'Sheet1'!$N$2:$N$9682;--($C$1>='Sheet1'!$I$2:$I$9682);--(('Sheet1'!$I$2:$I$9682)>=$D$1)));'Sheet1'!$N$2:$N$9682)

Where D1 is the starting date and C1 is the last date to consider.

 

[Jonmo1]

Which version of XL are you using, in XL2007+ you can use SUMIFS
Which allows for multiple criteria



=SUMIFS('Sheet1'!$N$2:$N$9682;'Sheet1'!$G$2:$G$9682;"*"&$A1&"/*";'Sheet1'!$I$2:$I$9682;"<=";$C$1,'Sheet1'!$I$2:$I$9682;">=";$D$1)

 

[wallaly]

Thank you Jonmo1!!

Now I notice that my formula was too complicated for something so simple!

It worked perfectly using:

=SUMIFS('Sheet1'!$N$2:$N$9682;'Sheet1'!$G$2:$G$9682;"*"&$A1&"/*";'Sheet1'!$I$2:$I$9682;"<="&$C$1;'Sheet1'!$I$2:$I$9682;">="&$D$1)

Thanks also Redwolfx for your help.

P.S. Took me some time to notice that a "&" was required to use "grater than" as a criteria in sumifs