Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: array pointing to a cell

  1. #1
    Guest

    Default

    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!


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •