Thread: Counting Instances of A String in Non-Contiguous Columns

Hey All,

I'm working on a little project and I've hit a stumbling block. I have a large excel worksheet in which column A is a list of email addresses. In columns B-AI I have an "x" demarcating whether or not the person in column A has opened an email or clicked on any of the links in that email. For example.

[EmailAddress]---OpenEmail1---ClicksEmail1---OpenEmail2---ClicksEmail2
[1@email.com]----------x--------------------x------------------#N/A------------#N/A
[2@email.com]----------x-----------------#N/A-------------------x-----------------x
etc.

Initially, I just wanted to count how many times each person was "engaging" with the email campaign, i.e. how many times "x" appeared in each row. That was simple enough: COUNTIF(B2:AI2), "x"). Easy.

Now I want to count only how many times each person has clicked on a link, i.e. how many times "x" appears in columns that have the word "Click" in their heading for each row. As you can see from the above examples, every other column is a "Click" column. COUNTIF only works on contiguous ranges and, because I'm working with about 3000 email addresses and so many email messages (and because I want to be able to update this with future email campaigns), I'm sort of loathe to rearrange the whole thing. So I tried to write up an array formula using SUM and ISNUMBER(FIND):

{=SUM((ISNUMBER(FIND("Clicks",$B$1:$AI$1))*(B2:AI2="x")))}

This should return the number of times "x" is found in range B2:AI2 where the column headings in range B1:AI1 contain the string "Clicks," correct? Correct. I know this because I tried it in a smaller example spreadsheet and it worked like a charm. When I moved the formula over to the larger spreadsheet, however, no dice.

One thing I should mention is that the collection of "x's" and "#N/A's" in the main worksheet are derived from the VLOOKUP function. In building this spreadsheet I took lists of people who opened and clicked links in each of our email campaigns, assigning each to it's own worksheet. I then put the letter x next to each email address, using VLOOKUP to search for the corresponding email address in the appropriate sheet and then report back the "x" that would demarcate it's place on that particular list, or the #N/A to show that it was *not* on that list. So what looks like an "x" would really be something like =VLOOKUP(A7,'72711'!$A$2:$B$1000,2,FALSE). Not super efficient, I know, but it was the best I could think of given the info I had access to.

So now I'm thinking the problem has to do with the possibility that the SUM function or array functions maybe can't reference values derived from a formula, but I would rather keep the formulas in place than turn them all into strings (using the "paste as" function, as was one of the ideas I've had to fix this). Is there any way I can do what I'm trying to do while leaving my worksheet as is?

Thanks so much for any help!

Can you upload example workbook?

Within XL 2007 you can use COUNTIFS formula:

=COUNTIFS($D$1:$G$1, "*"&"Clicks", D2:G2, "x")

You can also try this: removing error and create range by entering sheet name in the first row (Extend A2:B100 to as much as you need)..

Only when entering sheet name hit ' twice at the first character.

It should be said 2007 and newer...

Some formulas you can't use backwards (as COUNTIFS, SUMIFS in XL 2003) but always can be used in newer versions...

Here is solution for 2003 plus you don't need x's