SUMIFS / SUMPRODUCT with multiple criteria

[walduxas]

Hi,

I need to sumif values in certain column using multiple criteria. Here's what I came so far.
I could use Excel function SUMIFS:

Please Login or Register  to view this content.

But the issue with SUMIFS is that I have to use array criteria between brackets {}, but I'd like to use array criteria as a range instead. For instance,

Please Login or Register  to view this content.

I could use Excel function SUMPRODUCT:

Please Login or Register  to view this content.

But the issue with SUMPRODUCT is that wildcard "*" doesn't work, function searches for symbol "*", but I need it to search for any character. I need to use wildcard "*", because sometimes this criteria may be specified (for instance, Duomenys!$B:$B="ASD"), but sometimes I may need to sum everything.

So with SUMIFS I can use wildcard "*", but can't use array criteria as a range, and with SUMPRODUCT I can't use wildcard "*", but can use array criteria as a range.

My question: is it somehow possible to use wildcard "*" in SUMPRODUCT or to use array criteria as a range in SUMIFS?

[JohnTopley]

With SUMPRODUCT use ..

=SUMPRODUCT(--(ISNUMBER(SEARCH("*",B:B))))

or

=SUMPRODUCT(--(ISNUMBER(SEARCH("ASD",B:B))))

[walduxas]

I also thought of using SEARCH function. But it is looking for partial match. For instance, if there was object "ASDF", then SEARCH function would include this object and it would be summed.
However, when the criteria is specified, I need exact match, sum only "ASD" objects.

I also tried using MATCH function, but I couldn't find how to use wildcard "*" in it.

[samba_ravi]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.