SOLVED

Sumproduct with a wildcard condition

Brass 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 (Brass 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. :)
1 best response

Accepted Solutions
best response confirmed by QWeelon (Brass Contributor)
Solution

@QWeelon 

Without a helper column:

 

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

 

Corrected typo

View solution in original post