Make an Index/Match Sum Array with wildcards

[TheAlchemyst]

Hello everyone, looong time lurker, first time poster. I've grown to be quite proficient with Excel thanks to reading through these forums! However I haven't been able to find a solution to my current problem.

I have a series of data all on a single row. It works quite well for most of my purposes, but for this particular instance I'm running into an issue with collating related records. In the example below, I am doing a series of Index/Matches to find each example of a record from "Boyden" in order to sum up their unique visits. So for instance one record would have "cv.boyden.com" with the unique visits stored 2 columns after it. However, I have to list out every single permutation of boyden's sites individually, and there are quite a lot more than in the below example (.ch, .jp, etc).

Please Login or Register  to view this content.

It seems to me I should be able to make some sort of array formula (using CTRL+SHIFT+Enter) using wildcards (i.e. "*boyden*") to do this for me, however I tried a bunch of different ways to do it with no success. I don't want to make yet another intermediary page to reformat the data, so it has to pull from the single row from the other sheet. I'd greatly appreciate anyone who can point me in the right direction!

[FDibbins]

Hi and welcome to the forum

You may be way overthinking this? Have you taken a look at using trhe sumif() or sumifS() function? It sounds like 1 of these may be what you want. If uyou still have a problem, upload a sample workbook for me to play with.

See if this is along the lines or wehat you want?

	A	B
1	Name	Amt
2	aa1	1
3	aa1	2
4	bb1	3
5	bb2	4
6	cc1	5
7	cc2	6
8
9		3

B9=SUMIF($A$2:$A$7,"aa*",$B$2:$B$7)

[TheAlchemyst]

The problem is all the data is on a single row. Attached is an example (I hope):


sample.xlsx

[XOR LX]

Hi,

What's your expected result for the attached?

Regards

[TheAlchemyst]

Essentially I'm trying to do a SumIf from a single row. The formula from the OP works fine for every other instance, since they only have 1 or 2 permutations. But this particular firm has dozens of slightly different sites. I am trying to add up all the "New Users" for anything that matches "*Boyden*". However, I can't seem to make an array Match using wildcards like that, and Sumif doesn't work since all the data is stored in a single row.

[XOR LX]

and Sumif doesn't work since all the data is stored in a single row.

Why do you say this?

Assuming that "New Users" is always the next-but-one column on from "Name":

=SUMIF(B3:VK3,"*boyden*",D3:VM3)

Regards

[TheAlchemyst]

Well smoke me a kipper, that worked! I never thought of doing it like that.

[XOR LX]

Haha! Is it breakfast time already?

You're welcome!

[FDibbins]

Glad you got the answer you needed

You may have noticed that, apart from teh references, the formula I suggested...
=SUMIF($A$2:$A$7,"aa*",$B$2:$B$7)
is pretty much the same as XOR's...
=SUMIF(B3:VK3,"*boyden*",D3:VM3)