Using Arrays With IF functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carlsondaniel@gmail.com - 31 Oct 2006 01:23 GMT

Hi all, I am running into a problem. I am trying to sum values in a
table using arrays and I get the VALUE error. My formula is something
like this.

{=SUM((A1:A10=A15)*(B1:B10=B15)*(C1:C10))}

The values in A1:A10 and B1:B10 are manually inputted. The C1:C10 are
calculated using an IF functions and has some greater than, less than
equations within. My goal is to get the A and B columns to equal TRUE
and then multiply the C column and the Sum them all together.

I think my problem is the C column but not sure. Can this error be
cause by the IF function? I even tried making a new table, linking the
values from the original table to omit the large formulas - but I
keep getting the same error message. Any suggestions? Thanks for your
time.

Reply to this Message

Earl Kiosterud - 31 Oct 2006 02:55 GMT

Daniel,

For your formula to work, first of all, you need to enter it as an array
formula -- press Ctrl-Shift-Enter, not just Enter. It will only sum the
values in C1:C10 where the cell in A1:A10 in the same row equals A15 , and
the same for the value in B1:B10. Is this your objective?

You could also use this, which does not require entering as an array
formula:

=SUMPRODUCT((A1:A10=A15)*(B1:B10=B15)*(C1:C10 ))

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Hi all, I am running into a problem. I am trying to sum values in a
> table using arrays and I get the VALUE error. My formula is something
[quoted text clipped - 12 lines]
> keep getting the same error message. Any suggestions? Thanks for your
> time.

Reply to this Message

carlsondaniel@gmail.com - 31 Oct 2006 18:46 GMT

Hi Earl,

The product won't work because I am trying to extract and sum certain
values. I finally figured out what was wrong. I did not have any values
in some of the A and B columns so that is why it kept returing VALUE.
Thanks for your help!

Dan

> Daniel,
>
[quoted text clipped - 28 lines]
> > keep getting the same error message. Any suggestions? Thanks for your
> > time.

Reply to this Message

Rate this thread: