INDEX & MATCH problem (not again!)

[redart]

Hi All,
Can anyone help me with the following INDEX & MATCH problem?
I want the formula in B12 to count only those values (<=1) in the column whose header matches the value in A1. So for the current example the answer would be 3. If A1 contained "cc" the answer would be 2. "m" would give 3, and "a" would give 5. Instead it counts all the values from the desired column to the end column. Any suggestions?.

Book1.xls
	A	B	C	D	E	F
1	c		cc	c	m	a
2
3	m		13	7	4	-10
4	m		18	12	4	-7
5	c		1	-2	-6	-14
6	c		9	3	2	2
7	m		-2	11	7	-5
8	a		10	4	2	-6
9	m		8	0	-4	14
10	m		8	1	0	10
11
12		11
Sheet1

 

[Aladin Akyurek]

=COUNTIF(INDEX(C3:F10,0,MATCH(A1,C1:F1,0)),"<=1")

 

[redart]

Thanks Aladin, but I need to use the example formula in this :-

=SUMPRODUCT((INDEX(FL:FL,MATCH(L5,A1:A1371,1)):FL1371=INDEX(FL:FL,A12))*(INDEX(BH:BH,A12)=INDEX(BH:BH,MATCH(L5,A1:A1371,1)):BH1371)*(INDEX(BM:BP,MATCH(L5,A1:A1371,1),MATCH(INDEX(BC:BC,A12),BM12:BP12,0)):BP1371<=1)*(SUBTOTAL(3,OFFSET(INDEX(AY:AY,MATCH(L5,A1:A1371,1)):AY1371,ROW(AY1:INDEX(AY:AY,ROWS(INDEX(AY:AY,MATCH(L5,A1:A1371,1)):AY1371)))-1,,1))))

Sorry I can't firgure out how to highlight the section in question, but it starts at INDEX(BM:BP and ends at BP1371<=1. The formula needs to work under subtotalling, which I think rules out the COUNTIF function.

 

[Aladin Akyurek]

Thanks Aladin, but I need to use the example formula in this :-

=SUMPRODUCT((INDEX(FL:FL,MATCH(L5,A1:A1371,1)):FL1371=INDEX(FL:FL,A12))*(INDEX(BH:BH,A12)=INDEX(BH:BH,MATCH(L5,A1:A1371,1)):BH1371)*(INDEX(BM:BP,MATCH(L5,A1:A1371,1),MATCH(INDEX(BC:BC,A12),BM12:BP12,0)):BP1371<=1)*(SUBTOTAL(3,OFFSET(INDEX(AY:AY,MATCH(L5,A1:A1371,1)):AY1371,ROW(AY1:INDEX(AY:AY,ROWS(INDEX(AY:AY,MATCH(L5,A1:A1371,1)):AY1371)))-1,,1))))

Sorry I can't firgure out how to highlight the section in question, but it starts at INDEX(BM:BP and ends at BP1371<=1. The formula needs to work under subtotalling, which I think rules out the COUNTIF function.

Care to describe what the above formula is expected to achieve? Also, are you on Excel 2003?

 

[redart]

I was afraid you might ask that. OK...the equation is basically
=SUMPRODUCT((INDEX(FL:FL,MATCH(L5,A1:A1371,1)):FL1371=INDEX(FL:FL,A12))*(INDEX(BH:BH,A12)=INDEX(BH:BH,MATCH(L5,A1:A1371,1)):BH1371)*(INDEX(BM:BP,MATCH(L5,A1:A1371,1),MATCH(INDEX(BC:BC,A12),BM12:BP12,0)):BP1371<=1)),
as everything from 'SUBTOTAL' is just there to handle things when autofilter is used.

L5 contains a date, which is matched up in column A to give the starting row number for the calculation. A12 contains the row number of the currently active cell, which also acts as the row number for the end of the calculation. Columns FL and BH contain variables, the values of which must equal those in the active row to be counted in the summation. So for example, if INDEX(FL:FL,A12) contains the value "LS", and INDEX(BH:BH,A12) contains the values "Sr", then only those rows with "LS" in column FL and "Sr" in column BH will be counted. The last criteria is to match the value in INDEX(BC:BC,A12) with the column headers (contained in BM12:BP12) for array BM:BP. Whichever of the four columns has the same header as INDEX(BC:BC,A12) should be used as the third criteria to be included in the count, provided the corresponding value is <=1.
Clear as mud huh?. Oh, and I should have said..Excel 2000.

 

[Aladin Akyurek]

I was afraid you might ask that. OK...the equation is basically
=SUMPRODUCT((INDEX(FL:FL,MATCH(L5,A1:A1371,1)):FL1371=INDEX(FL:FL,A12))*(INDEX(BH:BH,A12)=INDEX(BH:BH,MATCH(L5,A1:A1371,1)):BH1371)*(INDEX(BM:BP,MATCH(L5,A1:A1371,1),MATCH(INDEX(BC:BC,A12),BM12:BP12,0)):BP1371<=1)),
as everything from 'SUBTOTAL' is just there to handle things when autofilter is used.

L5 contains a date, which is matched up in column A to give the starting row number for the calculation. A12 contains the row number of the currently active cell, which also acts as the row number for the end of the calculation. Columns FL and BH contain variables, the values of which must equal those in the active row to be counted in the summation. So for example, if INDEX(FL:FL,A12) contains the value "LS", and INDEX(BH:BH,A12) contains the values "Sr", then only those rows with "LS" in column FL and "Sr" in column BH will be counted. The last criteria is to match the value in INDEX(BC:BC,A12) with the column headers (contained in BM12:BP12) for array BM:BP. Whichever of the four columns has the same header as INDEX(BC:BC,A12) should be used as the third criteria to be included in the count, provided the corresponding value is <=1.
Clear as mud huh?. Oh, and I should have said..Excel 2000.

You want a conditional count regarding a data area you autofilter, right?

If so:

What is the data area including labels?

Which vertical range are you applying the auto filter?

What are the conditions and which vertical ranges must meet these conditions?

 

[redart]

Quote: "You want a conditional count regarding a data area you autofilter, right?" - That's right.

Quote: "If so:

What is the data area including labels? " - there are no labels apart from those in BM12:BP12. The data area is A16 to FS1371, however the starting row for the count depends on the chosen date in L5.

Which vertical range are you applying the auto filter? - Autofilter may or may not be applied - calculation needs to work either way. If applied could be to columns BC, BH and/or FL.

What are the conditions and which vertical ranges must meet these conditions? - The conditions change depending on the values contained in the active row. For example, if active row is row 1000, and the start date corresponds to row 500, then I want a count of how many times the values in BH500:BH999 equals BH1000, AND FL500:FL999 equals FL1000, AND whichever column, BM to BP has the label that corresponds to the value contained in BC1000 and whose values are <=1.

 

[Aladin Akyurek]

Quote: "You want a conditional count regarding a data area you autofilter, right?" - That's right.

Quote: "If so:

What is the data area including labels? " - there are no labels apart from those in BM12:BP12. The data area is A16 to FS1371, however the starting row for the count depends on the chosen date in L5.

Which vertical range are you applying the auto filter? - Autofilter may or may not be applied - calculation needs to work either way. If applied could be to columns BC, BH and/or FL.

What are the conditions and which vertical ranges must meet these conditions? - The conditions change depending on the values contained in the active row. For example, if active row is row 1000, and the start date corresponds to row 500, then I want a count of how many times the values in BH500:BH999 equals BH1000, AND FL500:FL999 equals FL1000, AND whichever column, BM to BP has the label that corresponds to the value contained in BC1000 and whose values are <=1.

A tip rather a worked-out formula:

The SUBTOTAL idiom works also when no autofilter is applied. Thus, that's not a concern.

SUMPRODUCT(SUBTOTAL(...),--Conditional,--Conditional,...)

should calculate the desired count without a need for specifying a calculated subrange as long as the date condition allows the formula to disregard irrelevant records.

 

[redart]

Aladin,
How does SUMPRODUCT(SUBTOTAL(...),--Conditional,--Conditional,...) differ from my original formula of
SUMPRODUCT(Conditional)*(Conditional)*...(SUBTOTAL(...)) , apart from the order, and the use of "--" instead of "*" ?.

 

[Aladin Akyurek]

Aladin,
How does SUMPRODUCT(SUBTOTAL(...),--Conditional,--Conditional,...) differ from my original formula of
SUMPRODUCT(Conditional)*(Conditional)*...(SUBTOTAL(...)) , apart from the order, and the use of "--" instead of "*" ?.

I did not look very closely at that longish formula, although I think my suggestion to eliminate the subrange calculation will yield a shorter fomula (also probably a less expensive one).