Apr 24 2021 11:18 AM - edited Apr 24 2021 11:27 AM
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
Apr 24 2021 11:46 AM
Apr 24 2021 11:59 AM - edited Apr 24 2021 12:52 PM
SolutionWithout a helper column:
=SUMPRODUCT(--ISNUMBER(FIND("Jan",N18:N22)),K18:K22,L18:L22)
Corrected typo
Apr 24 2021 12:12 PM
Apr 24 2021 12:15 PM
Apr 24 2021 12:24 PM
Apr 24 2021 12:31 PM
Strange. Please check in attached file.
Apr 24 2021 12:54 PM
I have corrected my previous reply. Thanks for pointing out the mistake!
Apr 24 2021 08:57 PM
Apr 24 2021 11:59 AM - edited Apr 24 2021 12:52 PM
SolutionWithout a helper column:
=SUMPRODUCT(--ISNUMBER(FIND("Jan",N18:N22)),K18:K22,L18:L22)
Corrected typo