# INDEX & MATCH problem (not again!)

#### redart

##### Board Regular
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
ABCDEF
1ccccma
2
3m1374-10
4m18124-7
5c1-2-6-14
6c9322
7m-2117-5
8a1042-6
9m80-414
10m81010
11
1211
Sheet1

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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

#### redart

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

##### MrExcel MVP
redart said:
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

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

##### MrExcel MVP
redart said:
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

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

##### MrExcel MVP
redart said:
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

##### Board Regular
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 "*" ?.

##### MrExcel MVP
redart said:
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).

Replies
9
Views
568
Replies
24
Views
431
Replies
3
Views
509
Replies
1
Views
391
Replies
0
Views
231

### Forum statistics

1,195,618
Messages
6,010,730
Members
441,566
Latest member
spimcom ### 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