View previous topic :: View next topic 
Author 
Message 
gradstudent 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?
Thanks in advance. 

Back to top 


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 

Back to top 


gradstudent 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. 

Back to top 


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.) 

Back to top 


gradstudent 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. 

Back to top 


