Embed an array?

  • Thread starter Thread starter JanetH
  • Start date Start date
J

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.
 
I should have clarified. The formula assumes your array is in rows 1 through
5. Adjust A1:A5 as necessary

Tyro
 
=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?
 
=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)
 
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!!!
 
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)))
JanetH said:
=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?
Click to expand...
 
What is the EXP column?

Tyro

JanetH said:
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!!!
Click to expand...
 
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)
 
the expenses array which has 4 columns.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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)



JanetH said:
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!!!
Click to expand...
 
The "obvious" is not always obvious.

Tyro

Bob Phillips said:
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)
Click to expand...
 
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?
 
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)
 
Back
Top