Count values in dynamic column greater than another

[chris01395]

I am trying to count all the values in column B that are greater than column C (on the same row) i.e B2 vs C2, B3 vs C3, B4 vs C4, the actual columns will change dynamically and I thought to use MATCH (as below) to identify column.

I had hoped following formula would work to avoid using INDIRECT and trying to keep it relatively simple as I will be evaluating thousands of rows of data.

=SUMPRODUCT(--(Index(tableA,0,match(A2,tableA[#Headers],0))> Index(tableA,0,match(NameXYZ,tableA[#Headers],0))))

Any suggestions gratefully received.

[Bob Phillips]

Why not just use

=SUMPRODUCT(--(B:B>C:C))

or if in a table, something like

=SUMPRODUCT(--(TableA[Colum B Header]>TableA[Colum C Header]))

[chris01395]

Thanks for the reply, the basic formula you propose is what I am aiming for but the columns are dynamic, I.e won’t always be column B and C.

[chris01395]

So seems my original formula (see below) does actually work, I altered the formatting of the table headers and this resolved my #N/A

=SUMPRODUCT(--(INDEX(TableA,0,MATCH(A2,TableA[#Headers],0))>INDEX(TableA,0,MATCH(NameXYZ,TableA[#Headers],0))))