array pointing to a cell

[Guest]

I'm slowly making progress with arrays, but don't have this one under my belt yet.

I want to sum a column based on three criteria and an array will do the job.

The problem is that I need one of the criteria to be the value in a cell (a variable).

Using one variable,
entered as an array, this works:
{=sum((A2:A100="887")*(F2:F100)+0}
where column A is the criterion and column F is the value to be summed.

Instead of hard coding the "887" I need the statement to read the value in another cell, let's say in H1.

I've tried ...(A2:A100=H1) ...
and ... (A2:A100="H1") ...
but neither work.

How do I use a variable criterion?

Thanks!

 

[Aladin Akyurek]

On 2002-03-07 09:11, Anonymous wrote:
I'm slowly making progress with arrays, but don't have this one under my belt yet.

I want to sum a column based on three criteria and an array will do the job.

The problem is that I need one of the criteria to be the value in a cell (a variable).

Using one variable,
entered as an array, this works:
{=sum((A2:A100="887")*(F2:F100)+0}
where column A is the criterion and column F is the value to be summed.

Instead of hard coding the "887" I need the statement to read the value in another cell, let's say in H1.

I've tried ...(A2:A100=H1) ...
and ... (A2:A100="H1") ...
but neither work.

How do I use a variable criterion?

Thanks!

Try:

{=SUM((A2:A100+0=H1)*(F2:F100))}

I suspect A2:A100 to be text formatted.

I guess you're experimenting with array formulas, but whenever you have a single condition, SUMIF will just what you want.

If A2:A100 is indeed text formatted,

=SUMIF(A2:A100,""&H1,F2:F100)

will also work.

 

[Don C]

Ahah! It was the addition of a zero in the array you taught me last week that got it to work. Putting the zero in a different place this time works. Someday I WILL understand this stuff.

I am actually combining three criteria, one is a variable, the other two are constants and can use ="F" to make them work, so sumif does not work.

The worksheet is the results of a large survey. Columns A, B, & C are demographic measures and the summaries (average scores for most of them) are a function of the possible values. B and C have only 2 and 3 possible values respectively, but A can be one of nearly 200 values; we have to print a report for each of them.

I think I can now clean make this report generator work much more efficiently!

Thanks