Saving space in Array formula Sum(If(())...

[Guest]

Hi.

I am usually pretty successful using the array formula Sum(IF(( but I am
having difficulty entering multiple criteria using the same range.

I check KB article 275165 and found if I copied the example in the article
to a worksheet it worked okay. However, when I apply it to my workbook, I
keep getting the N/A error result.

I am working with account balances by month and have a worksheet defined as
follows:

Month1 Month2 Month3 Month4 ..... Month12
Acct1
Acct2

 

[Guest]

Sorry! Forget this one!

 

[Guest]

Hi,
Can you be a little more specific, like the criteria you are trying to test
for in the Ifs? You might also show what you have tried? And some examples of
the data might help too.
Thanks,

 

[Guest]

Sorry about that. I thought I had accidentally completed this and re-did the
message. Here it is on another post:

Hi.

I am usually pretty successful using the SUM(IF(( array formula but am
having difficult when trying to use the { } with multiple criteria for one
range in the formula.

I have looked at KB article 275165 but haven't found this very helpful as I
keep getting the N/A error.

My worksheet is as follows:
colums
rows Month1 Month2 Month3 .... Month12
Acct1 Amt11 Amt21 ... Amt121
Acct2 Amt12 Amt22
.... ... ... ...
AcctN Amt1N Amt2N ... Amt12N

I need to sum over the account range (Acct1 - AcctN) for one month (Month x).
My formula is
SUM(IF((MonthRange=Monthx)*(AcctRange={Accta,Acctb}),Amt11:Amt12N,0))

It works so long as I have only Accta or Acctb (or if I connect several
criteria with AcctRange). I'd like it to know if there is a way to get it to
work in accordance with KB article 275165.

P.S. The accounts listed in the rows are number types (but I have tried it
while using strings as well).

What am I missing?!?!?!?!!?

Thanks!

 

[Tom Ogilvy]

See possible answer at your other posting of this question.