Embed an array?

[JanetH]

I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column
by X row array representing three types of income numbers, and Jan_Exp is the
same for expense numbers. This works fine.

Now I want to SUMIF to include only the rows in the array where there is a
"G" in the first column of the spreadsheet (not in the array). Is there a
way to do this?

Thanks.

 

[Tyro]

Enter this array formula:

Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp)))


Tyro

 

[Tyro]

I should have clarified. The formula assumes your array is in rows 1 through
5. Adjust A1:A5 as necessary

Tyro

 

[JanetH]

=SUMPRODUCT(--((Jan_G)="G")*((Jan_Inc)-(Jan_Exp)))

I tried the above - Jan_G is the "Name" of the range with the "G"'s, i.e.
Column A - but it returns an "NA". Thoughts?

 

[Bob Phillips]

=SUM(IF(A3:A5="G",Jan_Inc-Jan_Exp))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JanetH]

I got it! My problem was that the arrays for INC and EXP were not the same
size. One was 3 columns and the other was 4 columns. When I made them the
same size, it worked. Now I have to figure out how to include that fourth
EXP column!! Thanks!!!

 

[Tyro]

I used the following formula. The range A1:A5 is named Jan_G. The range
B1:C5 is named Jan_Inc. The range D1:E5 is named Jan_Exp. This formula
produces the correct answer:

=SUMPRODUCT(--(Jan_G="G")*(Jan_inc-Jan_Exp))

It does not need to be entered as an array formula.

Tyro


If I name A1:A5 as Jan_G this array formula works fine for me:
=SUMPRODUCT(--(Jan_G="G")*((Jan_inc)-(Jan_Exp)))

=SUMPRODUCT(--((Jan_G)="G")*((Jan_Inc)-(Jan_Exp)))

I tried the above - Jan_G is the "Name" of the range with the "G"'s, i.e.
Column A - but it returns an "NA". Thoughts?

 

[Tyro]

What is the EXP column?

Tyro

I got it! My problem was that the arrays for INC and EXP were not the same
size. One was 3 columns and the other was 4 columns. When I made them
the
same size, it worked. Now I have to figure out how to include that fourth
EXP column!! Thanks!!!

 

[Tyro]

Your formula works fine. But must be entered as an array formula

Tyro

 

[Bob Phillips]

seeing as he was already using an array formula, I guessed he might know
that!

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

the expenses array which has 4 columns.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

This will handle that

=SUM(IF(A3:A5="G",Jan_Inc),-IF(A3:A5="G",Jan_Exp))

it is an array formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

I got it! My problem was that the arrays for INC and EXP were not the same
size. One was 3 columns and the other was 4 columns. When I made them
the
same size, it worked. Now I have to figure out how to include that fourth
EXP column!! Thanks!!!

 

[Tyro]

The "obvious" is not always obvious.

Tyro

seeing as he was already using an array formula, I guessed he might know
that!

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[David Biddulph]

Why the double unary minus in that formula? Doesn't the multiplication
already do the job of coercing the boolean TRUE/FALSE to a number?

Wouldn't either
=SUMPRODUCT((Jan_G="G")*(Jan_inc-Jan_Exp)) or
=SUMPRODUCT(--(Jan_G="G"),(Jan_inc-Jan_Exp))
do the job?

 

[Bob Phillips]

I didn't say it was obvious, I explained the assumption that I made, which I
still say is valid.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)