Excel Array from cell range

Question

Hi Guys I'm trying to tweak a formula. I need to change the hardcoded array to reference a cell range.

=SUM(COUNTIFS(B1:B196,{"*yes*","*no*"},C1:C196,"agree"))

However I would like it to look like:

=SUM(COUNTIFS(B1:B196,A1;A2,C1:C196,"agree"))

The wild cards are important as I need to do a countif or within stings of text.

Wouldn't mind a VBA soulution.

Any help would be appreciated.

I was going to explain, but the answer below using concatenation is much better tbh. — SierraOscar
– SierraOscar, Commented Jul 30, 2015 at 0:29
You have a good answer below but in the interest of tweaking the formula, are the wildcards really necessary? Even if they are, a *no* or even a *yes* can produce a lot of false positives. — user4039065
– user4039065, Commented Jul 30, 2015 at 1:32

Doug Glancy · Accepted Answer · 2015-07-30 00:17:50Z

4

Enter this as an array formula with ctrl-shift-enter:

=SUM(COUNTIFS(B1:B196,"*" & A1:A2 & "*",C1:C196,"agree"))

answered Jul 30, 2015 at 0:17

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Although it should be noted that using SUMPRODUCT rather than SUM would also suffice, and would not require array-entry.

Thanks doug and @xor I decided to use sumproduct just for convenience. But used your formula doug cheers guys really appreciate it