Arithmetical Mode of Criteria in Multiple Non-Adjacent columns

[Sam via OfficeKB.com]

Hi All,

This is a variation to an earlier Posting resolved by Dominic.

Staying with Dominic's column referencing for the previous Post listed below;
I now wish to find the arithmetical MODE using the numerical value related to
each TEXT criterion located in the adjacent cell (to the left of the TEXT
criterion); i.e. Find the MODE value of the TEXT criterion "North" - the
corresponding numeric value is OFFSET one cell to the left of the TEXT
criterion "North" - same Row.

The Formula will still need to accommodate the 5 non-adjacent TEXT criteria
columns (B, D, F, H and J) to find all instances of "North" and the overall
MODE value.

Text criterion column B ="North" Numeric Value column A
Text criterion column D="North" Numeric Value column C
Text criterion column F="North" Numeric Value column E
Text criterion column H="North" Numeric Value column G
Text criterion column J="North" Numeric Value column I

All data starts from Row number one.
Row1 column B corresponds to the numeric value in Row1 column A
Row2 column B corresponds to the numeric value in Row2 column A
Row3 column B corresponds to the numeric value in Row3 column A etc.

Row1 column D corresponds to the numeric value in Row1 column C
Row2 column D corresponds to the numeric value in Row2 column C
Row3 column D corresponds to the numeric value in Row3 column C etc.


Thanks
Sam
------------------------------------------------------------------------------
---------------------
Previous Posting:-
http://www.officekb.com/Uwe/Forum.a...tiple-Non-Adjacent#[email protected]


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?

Dominic's working Formula:
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.

 

[Domenic]

Try...

=MODE(IF(B1:J10="North",A1:I10))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for reply and ongoing help. Formula provides correct result.

Can this Formula be adapted to provide results for ONLY Visible Filtered
cells.

=MODE(IF(B1:J10="North",A1:I10))

....confirmed with CONTROL+SHIFT+ENTER.

Thanks
Sam

Try...

=MODE(IF(B1:J10="North",A1:I10))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi All,

[quoted text clipped - 55 lines]

the right, in this case Column D, and so on. Also, adjust the range
accordingly.

 

[Domenic]

Try...

=MODE(IF((SUBTOTAL(3,OFFSET(B1:J10,ROW(B1:J10)-MIN(ROW(B1:J10)),0,1))>0)*
(B1:J10="North"),A1:I10))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you, the Formula works great!

Cheers
Sam

Try...

=MODE(IF((SUBTOTAL(3,OFFSET(B1:J10,ROW(B1:J10)-MIN(ROW(B1:J10)),0,1))>0)*
(B1:J10="North"),A1:I10))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Domenic,

[quoted text clipped - 9 lines]

Thanks
Sam