dynamic row numbers within a formula

[flashbbeb]

I'm using the SUMPRODUCT formula to calculate the number of times two different conditions are met: that in a column the value equals whatever is in a specific cell.

What I would like to be able to do is refer to one of cells by a row number, so it dynamically adds the number without me having to specify it.

In the example below, where $B173 is, I want to dynamically refer to B and whatever number.
=SUMPRODUCT(--(ILTA!$A$1:ILTA!$A$50000=$B173),--(ILTA!$H$1:ILTA!$H$50000=F2))

After the first equals sign, I have tried substituting the following for $B173 and received an incorrect count or an error:

1) "$B"&ROW()
2) $B&ROW()

Is there a way for this to work?

Thanks,
EB

 

[SkipVought]

Hi,
[tt]
INDIRECT("$B"&ROW())
[/tt]
But WHY would you do this since when you COPY the formula to another row, THAT IS THE ROW that is referred to?

I am totally puzzeled!

Also you have TWO DIFFERENT ROWS specificed in your formula: one absolute to the COLUMN, the other relative. Can you explain that?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[flashbbeb]

I'll give the indirect a try.

I'm referring to the name of a course in the B column (this is a long list), where 14 different department names are listed above across the top (starting with cell F2).

The count is how many course completions from a specific department.

What I'd like to be able to do is 1) fill the formula down for rows below it (where the B & ROW() refers to the course name in the column), then 2) fill right so the F2 cell increments by G, H, I, etc.)

That's why I'm using the absolute reference for range A1 through A50000 on sheet "ILTA". When I fill down, I don't want that absolute reference to increment.

I want the F2 row reference to increment horizontally, and the B173 (or really B "number") to increment vertically with each row number.

Thanks,
EB

 

[SkipVought]

Then no need to INDIRECT...
[tt]
=SUMPRODUCT(--(ILTA!$A$1:ILTA!$A$50000=$B173),--(ILTA!$H$1:ILTA!$H$50000=F[red]$[/red]2))
[/tt]
this will make row 2 absolute as well as column B.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Tip:

select a reference in a formula (range or single cell).

hit the F4 key FOUR TIMES and watch the reference.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]