Counta

[bh414]

Hi,

I'm trying to create a dynamic formula that will apply to multiple tables with multiple minimums. Basically, I am trying to first identify the cell that holds the minimum, then count the cells from that minimum down the column, until the cell value becomes greater than 1. So far, I have the following formula:

=COUNTA(INDIRECT(ADDRESS(MATCH(MIN(C5:BK65),A:A,FALSE), MATCH(MIN(C5:BK65),4:4,FALSE))):K17)

The first part (beginning with Indirect), identifies the cell containing the minimum (K14), but I can't figure out how to structure the formula that would return K17. And it must be dynamic, as K14 and K17 will vary depending on which table is being read.

Any help is appreciated!

 

[StephenCrump]

Welcome to MrExcel!

Can you show us what your table looks like, and which cells you're trying to add in the COUNTA() ?

It's not clear why you say that K14 contains the minimum. If the first part of your formula returns K14, it means only that the minimum value occurs in row 14 of column A, and also in column K of row 4. The value in K14 could be anything?

 

[bh414]

Welcome to MrExcel!

Can you show us what your table looks like, and which cells you're trying to add in the COUNTA() ?

It's not clear why you say that K14 contains the minimum. If the first part of your formula returns K14, it means only that the minimum value occurs in row 14 of column A, and also in column K of row 4. The value in K14 could be anything?

Sorry for the confusion, first time posting. I'm dealing with 20 different tables, all with minimums in different cells and with different count ranges. Below is an example of one of the tables. Column A and Row 1 serve to identify the minimums, and return FALSE if the minimum is not contained. It was a way to match things up for another formula in the same spreadsheet. But these values will also change table to table.

I need to find the minimum value, then count down the column from the minimum value until a cell reaches greater than or equal to 1. So in this example, the minimum is found in J11, and I need to COUNTA(J11:J14).

So far, I am able to find the minimum for the beginning of the count range. But I need a formula to identify J14, or the first value in the column >1, that is dynamic.

=COUNTA(INDIRECT(ADDRESS(MATCH(MIN(C3:O15),A:A,FALSE), MATCH(MIN(C3:O15),1:1,FALSE))):J14)

Thanks!

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	0.9553972	FALSE	FALSE	FALSE	FALSE	FALSE
2		Date	1/1/1990	2/1/1990	3/1/1990	4/1/1990	5/1/1990	6/1/1990	7/1/1990	8/1/1990	9/1/1990	10/1/1990	11/1/1990	12/1/1990	1/1/1991
3	FALSE	1/1/1990	0.985
4	FALSE	2/1/1990	0.986	1.001
5	FALSE	3/1/1990	0.985	1.000	0.999
6	FALSE	4/1/1990	0.980	0.995	0.994	0.995
7	FALSE	5/1/1990	1.014	1.030	1.029	1.030	1.035
8	FALSE	6/1/1990	1.022	1.037	1.036	1.037	1.043	1.007
9	FALSE	7/1/1990	1.031	1.046	1.045	1.046	1.051	1.016	1.009
10	FALSE	8/1/1990	1.003	1.018	1.017	1.018	1.023	0.989	0.981	0.973
11	0.955397	9/1/1990	0.985	1.000	0.998	0.999	1.005	0.971	0.964	0.955	0.982
12	FALSE	10/1/1990	1.001	1.016	1.014	1.015	1.021	0.986	0.979	0.971	0.998	1.016
13	FALSE	11/1/1990	1.015	1.030	1.029	1.030	1.035	1.000	0.993	0.984	1.012	1.030	1.014
14	FALSE	12/1/1990	1.029	1.044	1.043	1.044	1.049	1.014	1.007	0.998	1.026	1.045	1.028	1.014
15	FALSE	1/1/1991	1.048	1.064	1.063	1.064	1.069	1.033	1.026	1.017	1.045	1.064	1.048	1.033	1.019

<tbody>
</tbody>

 

[StephenCrump]

Given a data range: MyRange, then

RowNo: =SUMPRODUCT(--(MyRange=MIN(MyRange))*ROW(MyRange))
ColNo: =SUMPRODUCT(--(MyRange=MIN(MyRange))*COLUMN(MyRange))

will give you the row number and column number respectively of the minimum.

And I think the result you're looking for will be:

=SUMPRODUCT(--(INDEX(MyRange,,1+ColNo-COLUMN(MyRange))<1),--(ROW(MyRange)>=RowNo))

But be careful, if there is more than one minimum, you will get an incorrect result. You can test for this using:

=COUNTIF(MyRange,MIN(MyRange))>1

If you're open to using VBA, a UDF approach could also be used.

 

[bh414]

Given a data range: MyRange, then

RowNo: =SUMPRODUCT(--(MyRange=MIN(MyRange))*ROW(MyRange))
ColNo: =SUMPRODUCT(--(MyRange=MIN(MyRange))*COLUMN(MyRange))

will give you the row number and column number respectively of the minimum.

And I think the result you're looking for will be:

=SUMPRODUCT(--(INDEX(MyRange,,1+ColNo-COLUMN(MyRange))<1),--(ROW(MyRange)>=RowNo))

But be careful, if there is more than one minimum, you will get an incorrect result. You can test for this using:

=COUNTIF(MyRange,MIN(MyRange))>1

If you're open to using VBA, a UDF approach could also be used.

Thank you so much! This works perfectly. Could you please give a little explanation on the methodology?

 

[StephenCrump]

Thank you so much! This works perfectly. Could you please give a little explanation on the methodology?

Let's use a simpler example to illustrate:

Excel 2010

	A	B	C	D
1
2		9	9	9
3		9	7	0.8
4		9	9	0.9
5
6		Results:
7		RowNo	3
8		ColNo	4
9		Count	2

<tbody>
</tbody>

Sheet1​

We're working with arrays. If you edit each formula using F2, and use F9 to evaluate each component, it will show something like:

RowNo:
=SUMPRODUCT(--(MyRange=MIN(MyRange))*ROW(MyRange))
=SUMPRODUCT(--({FALSE,FALSE,FALSE;FALSE,FALSE,TRUE;FALSE,FALSE,FALSE})*{2;3;4})
=SUMPRODUCT({0,0,0;0,0,1;0,0,0}*{2;3;4})
=SUMPRODUCT({0,0,0;0,0,3;0,0,0})
=3

and similar for ColNo.

Count:
ColNo = 4, but because MyRange starts in column 2, we want the 3rd column of MyRange, which is:
=INDEX(MyRange,,1+ColNo-COLUMN(MyRange))
={9;0.8;0.9}

(There's a bit of a fudge here .. COLUMN(MyRange) actually returns {2,3,4} but INDEX returns only the first column = 2).

So ... SUMPRODUCT(--(INDEX(MyRange,,1+ColNo-COLUMN(MyRange))<1),--(ROW(MyRange)>=RowNo))
=SUMPRODUCT(--({9;0.8;0.9}<1),--({2;3;4}>=RowNo))
=SUMPRODUCT(--({FALSE;TRUE;TRUE}),--({FALSE;TRUE;TRUE}))
=SUMPRODUCT({0;1;1},{0;1;1})
=2