Assuming that Columns B, D, F, H, and J are your five non-adjacent
columns, and that you want to count the total number of times the value
'North' is contained in those columns, whether the data is filtered or
not, try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),{0,2,4,
6,8},1))*(T(OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),{0,2,4,6,8},1))="N
orth"))

To change the columns being evaluated, adjust the constant array
{0,2,4,6,8}. The 0 refers to the column being referenced or starting
point, in this case Column B. The 2 refers to the number of columns to
the right, in this case Column D, and so on. Also, adjust the range
accordingly.

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

I wish to sum the count of a single TEXT criteria that is located in several
(5) non-adjacent columns - hundreds of rows. Also, should I choose to apply
filters: I require the Formula to show the summed count of ONLY Visible
Filtered cells. How can this best be achieved with minimum calculation /
processing overhead?

I located this Formula on
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1 :$A$100)-ROW
(INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10)

However, I am not sure if it is feasible to reference my 5 non-adjacent
columns based on the above Formula, perhaps a more suitable solution exists?

Thanks
Sam