# Countifs or Sumproduct

#### hayden

##### Board Regular
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.
Code:
``````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
Code:
``````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.

Hayden

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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))

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

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!

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!

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.

Replies
0
Views
59
Replies
9
Views
672
Replies
1
Views
168
Replies
1
Views
36
Replies
12
Views
128

1,203,070
Messages
6,053,364
Members
444,657
Latest member
jessejames1of3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back