SOLVED

Sumproduct with a wildcard condition

%3CLINGO-SUB%20id%3D%22lingo-sub-2286666%22%20slang%3D%22en-US%22%3ESumproduct%20with%20a%20wildcard%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286666%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20struggling%20with%20a%20formula%20where%20I%20want%20to%20use%20a%20wildcard%20condition%20to%20sum%20and%20multiply%20values%20if%20the%20corresponding%20month%20is%20mentioned.%20In%20other%20words%2C%20for%20January%2C%20I%20want%20to%20wildcard%20*Jan*%2C%20for%20February%20it's%20*Feb*%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20using%20the%20formula%20below%2C%20but%20the%20wildcard%20doesn't%20seem%20to%20work%20at%20all%2C%20so%20I'm%20figuring%20I'm%20doing%20something%20wrong.%20I%20have%20verified%20the%20formula%20without%20the%20wildcards%20without%20issues.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(--(N18%3AN22%3D%22*Jan*%22)%2CK18%3AK22%2CL18%3AL22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20output%20for%20the%20above%20formula%20ought%20to%20be%2080%2C%20but%20Excel's%20output%20is%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20use%20wildcards%20in%20this%20scenario%2C%20or%20is%20it%20a%20better%2Fanother%20way%20to%20solve%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%2FQ%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Excel.PNG%22%20style%3D%22width%3A%20469px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275278i742002B8BDC54637%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel.PNG%22%20alt%3D%22Excel.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CDIV%20class%3D%22gtx-trans-icon%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2286666%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286772%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20with%20a%20wildcard%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286772%22%20slang%3D%22en-US%22%3ESolved%20it%20by%20adding%20a%20column%20and%20used%20SUMIF%20instead.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286781%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20with%20a%20wildcard%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286781%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F912606%22%20target%3D%22_blank%22%3E%40QWeelon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWithout%20a%20helper%20column%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(--ISNUMBER(FIND(%2C%22Jan%22%2CN18%3AN22))%2CK18%3AK22%2CL18%3AL22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286788%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20with%20a%20wildcard%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286788%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F912606%22%20target%3D%22_blank%22%3E%40QWeelon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS(K%3AK%2CN%3AN%2C%22*Jan*%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286789%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20with%20a%20wildcard%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286789%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20answer!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20figured%20there%20ought%20to%20be%20a%20way%2C%20although%20the%20formula%20don't%20seem%20to%20work%20for%20me.%20I%20still%20get%20a%200%20in%20output.%20I'm%20using%20365..%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%3C%2FLINGO-BODY%3E
Contributor

Dear community,

 

I'm struggling with a formula where I want to use a wildcard condition to sum and multiply values if the corresponding month is mentioned. In other words, for January, I want to wildcard *Jan*, for February it's *Feb* and so on.

 

I've been using the formula below, but the wildcard doesn't seem to work at all, so I'm figuring I'm doing something wrong. I have verified the formula without the wildcards without issues.

 

=SUMPRODUCT(--(N18:N22="*Jan*"),K18:K22,L18:L22)

 

The output for the above formula ought to be 80, but Excel's output is 0.

 

Is it possible to use wildcards in this scenario, or is it a better/another way to solve it?

 

Thanks in advance

 

/Q


Excel.PNG

 
 
 
 
 
 
 
 
8 Replies
Solved it by adding a column and used SUMIF instead.

Thanks.
best response confirmed by QWeelon (Contributor)
Solution

@QWeelon 

Without a helper column:

 

=SUMPRODUCT(--ISNUMBER(FIND("Jan",N18:N22)),K18:K22,L18:L22)

 

Corrected typo

@QWeelon 

As variant

=SUMIFS(K:K,N:N,"*Jan*")
Hi,

Thank you for answer!

I figured there ought to be a way, although the formula don't seem to work for me. I still get a 0 in output. I'm using 365..

Regards
@Hans Vogelaar,

Did some error searching and it worked like a charm when I removed the comma before "Jan". Thank you!

@Sergei Baklan,
Neat, that's one clean formula. I'm amazed by how simple some things are with the right knowledge and formulas. Very nice indeed!

=SUMPRODUCT(--ISNUMBER(FIND("Jan",N18:N22)),K18:K22,L18:L22)

@QWeelon 

Strange. Please check in attached file.

@QWeelon 

I have corrected my previous reply. Thanks for pointing out the mistake!

Yes, but the question regarded sumproducts.