Sum Count of Single Criteria in Multiple Non-Adjacent columns

[Sam via OfficeKB.com]

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

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you so much - Formula does the job!

Cheers
Sam

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!

Hi All,

[quoted text clipped - 14 lines]

Thanks
Sam

 

[Domenic]

Another way would be to use the column headings to choose the ones you
want evaluated. So, for example, if Columns B through J contain your
data, and the first row contains your headers/labels, try...

=SUM(MMULT((SUBTOTAL(3,OFFSET(B2:J10,ROW(B2:J10)-MIN(ROW(B2:J10)),0,1))>0
)*(B2:J10="North"),TRANSPOSE(ISNUMBER(MATCH(B1:J1,{"Header1","Header2","H
eader3","Header4","Header5"},0)))*1))

....where Header1, Header2, etc., are the headings for the columns you
want evaluated. Replace these with your actual headings.

or

=SUM(MMULT((SUBTOTAL(3,OFFSET(B2:J10,ROW(B2:J10)-MIN(ROW(B2:J10)),0,1))>0
)*(B2:J10="North"),TRANSPOSE(ISNUMBER(MATCH(B1:J1,L2:L6,0)))*1))

....where L2:L6 contains your list of column headings, indicating the
columns you want evaluated. Both formulas need to be confirmed with
CONTROL+SHIFT+ENTER.

In terms of efficiency, I don't know which one is more efficient. But,
personally, I prefer either of these two formulas as opposed to the one
I offer in my first post.

Hope this helps!

Hi Domenic,

Thank you so much - Formula does the job!

Cheers
Sam

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!

Hi All,

[quoted text clipped - 14 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks a lot for the alternative solutions - showing various ways to arrive
at the same end result. Very much appreciated.

With regard to headers: the data I'm using was extracted from another
worksheet and now starts in "Row number one" of each respective column, does
this mean the other two solutions are not viable if my data is in "Row number
one" of the worksheet?

Cheers
Sam

Another way would be to use the column headings to choose the ones you
want evaluated. So, for example, if Columns B through J contain your
data, and the first row contains your headers/labels, try...

=SUM(MMULT((SUBTOTAL(3,OFFSET(B2:J10,ROW(B2:J10)-MIN(ROW(B2:J10)),0,1))>0
)*(B2:J10="North"),TRANSPOSE(ISNUMBER(MATCH(B1:J1,{"Header1","Header2","H
eader3","Header4","Header5"},0)))*1))

...where Header1, Header2, etc., are the headings for the columns you
want evaluated. Replace these with your actual headings.

or

=SUM(MMULT((SUBTOTAL(3,OFFSET(B2:J10,ROW(B2:J10)-MIN(ROW(B2:J10)),0,1))>0
)*(B2:J10="North"),TRANSPOSE(ISNUMBER(MATCH(B1:J1,L2:L6,0)))*1))

...where L2:L6 contains your list of column headings, indicating the
columns you want evaluated. Both formulas need to be confirmed with
CONTROL+SHIFT+ENTER.

In terms of efficiency, I don't know which one is more efficient. But,
personally, I prefer either of these two formulas as opposed to the one
I offer in my first post.

Hope this helps!

Hi Domenic,

[quoted text clipped - 25 lines]

 

[Domenic]

You say your data starts in Row 1. Does that mean that you have no
headers and that you won't be filtering your data?

 

[Domenic]

I've just noticed (and replied to) your other post and realized that the
values for the columns you want evaluated differ from the ones in the
adjacent columns. So, if in fact you have no headers and are not
concerned with filtered data, the following formula should suffice...

=SUMPRODUCT(--(A1:J10="North"))

....which will count all cells in the range A1:J10 that contain the value
"North". Since Columns A, C, E, G, and I will never contain the value
"North", they won't be counted.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Yes, I have no headers but some data will be filtered.

Thanks
Sam

You say your data starts in Row 1. Does that mean that you have no
headers and that you won't be filtering your data?

Hi Domenic,

[quoted text clipped - 8 lines]

Cheers
Sam

 

[Domenic]

Try...

=SUMPRODUCT((SUBTOTAL(3,OFFSET(A1:J10,ROW(A1:J10)-MIN(ROW(A1:J10)),0,1))>
0)*(A1:J10="North"))

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for clarification.

Cheers,
Sam

I've just noticed (and replied to) your other post and realized that the
values for the columns you want evaluated differ from the ones in the
adjacent columns. So, if in fact you have no headers and are not
concerned with filtered data, the following formula should suffice...

=SUMPRODUCT(--(A1:J10="North"))

...which will count all cells in the range A1:J10 that contain the value
"North". Since Columns A, C, E, G, and I will never contain the value
"North", they won't be counted.

Hope this helps!

Hi Domenic,

[quoted text clipped - 8 lines]

Cheers
Sam