Using Arrays With IF functions

[carlsondaniel]

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.

 

[Earl Kiosterud]

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 ))

 

[carlsondaniel]

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