Sum Count of Single Criteria in Multiple Non-Adjacent columns

  • Thread starter Sam via OfficeKB.com
  • Start date

S

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
 
Ad

Advertisements

D

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!
 
S

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!
[quoted text clipped - 14 lines]
Thanks
Sam
 
D

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!

Sam via OfficeKB.com said:
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!
[quoted text clipped - 14 lines]
Thanks
Sam
 
S

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]
 
D

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?
 
Ad

Advertisements

D

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!
 
D

Domenic

Try...

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

Hope this helps!
 
Ad

Advertisements

S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top