Countifs or Sumproduct

[hayden]

Hello Everyone,

Thanks again for your time. I am trying to count the number of occurrences of a minimum. An simplified example is probably best:
I have a matrix of values and want to count the number of times a minimum for the row occurs for each column. For example my matrix looks something like this [3 rows x 4 columns]. Each Row has a unique identifier I could use in a count if, as does each column if that helps.

Row 1 - 50,50,100,100
Row 2 - 20,25,30,30
Row 3 - 75,80,70,75

The result would look something like this

Col 1 - 2  (Min in row 1 and row 2)
Col 2 - 1  (Min in row 1)
Col 3 - 1  (Min in row 3)
Col 4 - 0  (No Min in any row)

I've tried various countifs but haven't been able to make it work. I'm wondering if sumproduct might be better... but I'm not good with that one.

Thanks again for your time!

Hayden

 

[circledchicken]

Hi,

I'm a bit confused by your wording around rows and columns, but perhaps try something like this (adjusting the ranges as appropriate) and drag down:

=COUNTIF(A1:D1, MIN(A1:D1))

 

[hayden]

Thanks for your reply. Let me try and clarify. This data is from a ground water model and represents a water level in each model cell for each time period. Each row is a unique ground water model cell, and each column is a month. For example in row 1 (i.e. model cell 1) the water level was at 50 feet in month 1, 50 feet in month 2 , 100 feet in month 3, and 100 feet in month 4.

What I am trying to do is determine what month was the driest, i.e. had the lowest water level in the most cells. So I need to count the minimums for each month (i.e. column) but each row has a different minimum (i.e. each model cell has its own minimum value).

 

[hayden]

Well I found a brute force way... but there has to be a simpler way. I created another matrix that was 3 rows, and 4 columns and used an if statement to return a value of 1 if the cell matched the minimum [if a1=min(a1:d1),1,0)], that gave me a 1 everywhere I had a minimum. Then I summed each column to get a count of the number of cells with a minimum in each month.

If you have a easier way I'm all ears!

 

[hayden]

Well I found a brute force way... but there has to be a simpler way. I created another matrix that was 3 rows, and 4 columns and used an if statement to return a value of 1 if the cell matched the minimum [if a1=min(a1:d1),1,0)], that gave me a 1 everywhere I had a minimum. Then I summed each column to get a count of the number of cells with a minimum in each month.

If you have a easier way I'm all ears!

 

[circledchicken]

Perhaps, if possible, restructure your data in the following format:

Column A - Month
Column B - Model cell
Column C - Water level
Column D - Minimum

Column D could have a formula something like this (array entered with CTRL-SHIFT-ENTER rather than just ENTER):
=--(C2 = MIN(IF(B$2:B$13 = B2, C$2:C$13)))

A pivot table can then be used to sum and order the number of minimums for each month.