[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Newbie

Joined: 21 Nov 2008
Posts: 3

Posted: Fri Nov 21, 2008 11:16 am    Post subject: countif with condition depending on multiple fields

Hi,

I'm trying to write a spreadsheet formula to determine for each column how many of the columns values are maximum for that row. I've added a column that contains the maximum value for the row, but can't figure out how to write the countif condition that would compare a column to that column (rather than to a single value). To give an example, I have:

 Code: data1 data2 data3  max  10    5     3     10  12    5     8     12  7    14     9     14

I would like to be able to get from this data the row (2, 1, 0), since data1 is maximal twice, data2 is maximal once, data3 0 times. The number of different columns in the spreadsheet is going to be changing, so I don't want to introduce a new column for each data column with a 1 if it is maximal and 0 if not and then count that. Is there any way to do this?

Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Fri Nov 21, 2008 1:28 pm    Post subject: E2: =MATCH(\$D2;\$A2:\$C2;0) [copy down] A5: =COUNTIF(D\$2:D\$4;COLUMN(A1)) [copy to right]_________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
Newbie

Joined: 21 Nov 2008
Posts: 3

Posted: Mon Nov 24, 2008 6:09 am    Post subject:

 Villeroy wrote: E2: =MATCH(\$D2;\$A2:\$C2;0) [copy down] A5: =COUNTIF(D\$2\$4;COLUMN(A1)) [copy to right]

Thanks for the reply, and this is fairly close to what I want. The only problem however is that multiple columns may contain the minimum, in which case i want to count all of them. With this formula the cell will be counted only if it contains the *first* minimum value in the row (because match only displays the first occurrence of the minimum). Is there anyway to work around this?

To give an example, If the input were:
 Code: data1 data2 10     5 8      8

I would want to get 2, 1, in the bottom row (column 1 is maximal in rows 1 and 2, column 2 is maximal in row 2).

Ideally what I would do is a pairwise comparison of each column to the max column and count the rows in which they are equal, but there doesn't seem to be any way to do this.
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

 Posted: Mon Nov 24, 2008 9:40 am    Post subject: What about using =SUMPRODUCT(A\$2:A\$4=\$D\$2:\$D\$4) (Cell A5 in that first example) (I know, it seems a little weird with a product from just one factor, but it works if I'm not terribly mistaken.)
Newbie

Joined: 21 Nov 2008
Posts: 3

Posted: Tue Nov 25, 2008 5:04 am    Post subject:

 keme wrote: What about using =SUMPRODUCT(A\$2:A\$4=\$D\$2:\$D\$4) (Cell A5 in that first example) (I know, it seems a little weird with a product from just one factor, but it works if I'm not terribly mistaken.)

Thanks a lot, I got this to work after some playing around (I had some blank cells that weren't treated properly etc.) I assume this works because true evaluates to 1, false to zero, and the product of a single value is considered to be the value itself?

Cheers.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum