How to add in an array formula if iisna index match
 

Hello,

Can anyone help me with adding two index matches together.

I have the following formula which searches for the city of chicago in the
county of cook if NOT true return zero if true return column 4.

However I need the formula to search as above but also search for Chicago in
Du Page county and return the total of both.

How can I accomplish this in a formula? Is it possible?

=IF(ISNA(INDEX(ilsort,MATCH(1,(INDEX(ilsort,,1)="T otals for City:
CHICAGO")*(INDEX(ilsort,,2)=" County:
COOK"),0),4)),0,INDEX(ilsort,MATCH(1,(INDEX(ilsort ,,1)="Totals for City:
CHICAGO")*(INDEX(ilsort,,2)=" County: COOK"),0),4))

Thanks for your help

Hi!

Try this, it's much shorter:

=SUMPRODUCT(--(INDEX(ilsort,,1)="chicago"),--(INDEX
(ilsort,,2)="cook")+(INDEX(ilsort,,2)="page"),INDE X
(ilsort,,4))

Of course, you need to use the actual criteria names in
place of my shortcuts!

OR, you can use cell references:

=SUMPRODUCT(--(INDEX(ilsort,,1)=A1),--(INDEX(ilsort,,2)=B1)
+(INDEX(ilsort,,2)=C1),INDEX(ilsort,,4))

Biff

-----Original Message-----
Hello,

Can anyone help me with adding two index matches together.

I have the following formula which searches for the city
of chicago in the
county of cook if NOT true return zero if true return
column 4.

However I need the formula to search as above but also
search for Chicago in
Du Page county and return the total of both.

How can I accomplish this in a formula? Is it possible?

=IF(ISNA(INDEX(ilsort,MATCH(1,(INDEX(ilsort,,1)=" Totals
for City:
CHICAGO")*(INDEX(ilsort,,2)=" County:
COOK"),0),4)),0,INDEX(ilsort,MATCH(1,(INDEX(ilsor t,,1)
="Totals for City:
CHICAGO")*(INDEX(ilsort,,2)=" County: COOK"),0),4))

Thanks for your help
.

WOW, that worked great!. I'm beginning to dabble with these intense
formulas can you tell me what the -- represent.

Even though I use the help feature to try and figures things out at times I
just don't understand what or why a function would come first when there are
multiple functions in a formula. Is there somewhere here in KB search that
can explain this?

thank you so much for your help.



"Biff" wrote:

Hi!

Try this, it's much shorter:

=SUMPRODUCT(--(INDEX(ilsort,,1)="chicago"),--(INDEX
(ilsort,,2)="cook")+(INDEX(ilsort,,2)="page"),INDE X
(ilsort,,4))

Of course, you need to use the actual criteria names in
place of my shortcuts!

OR, you can use cell references:

=SUMPRODUCT(--(INDEX(ilsort,,1)=A1),--(INDEX(ilsort,,2)=B1)
+(INDEX(ilsort,,2)=C1),INDEX(ilsort,,4))

Biff

-----Original Message-----
Hello,

Can anyone help me with adding two index matches together.

I have the following formula which searches for the city
of chicago in the
county of cook if NOT true return zero if true return
column 4.

However I need the formula to search as above but also
search for Chicago in
Du Page county and return the total of both.

How can I accomplish this in a formula? Is it possible?

=IF(ISNA(INDEX(ilsort,MATCH(1,(INDEX(ilsort,,1)=" Totals
for City:
CHICAGO")*(INDEX(ilsort,,2)=" County:
COOK"),0),4)),0,INDEX(ilsort,MATCH(1,(INDEX(ilsor t,,1)
="Totals for City:
CHICAGO")*(INDEX(ilsort,,2)=" County: COOK"),0),4))

Thanks for your help
.

Hi!

See this:

http://mcgimpsey.com/excel/formulae/doubleneg.html

The best way to learn, IMO, is to read the posts you find
here in the newsgroup. Read the questions, think of what
you would do for a solution, then read the replies to the
question. Books are OK, but very few books can replicate
the "real world" situations that you find in these
newsgroups.

I have stacks of books but 90% of what I have learned is
from right here in the newsgroups. There are some truly
brilliant people that answer posts. I wish I was one of
them!

Biff

-----Original Message-----
WOW, that worked great!. I'm beginning to dabble with
these intense
formulas can you tell me what the -- represent.

Even though I use the help feature to try and figures
things out at times I
just don't understand what or why a function would come
first when there are
multiple functions in a formula. Is there somewhere here
in KB search that
can explain this?

thank you so much for your help.



"Biff" wrote:

Hi!

Try this, it's much shorter:

=SUMPRODUCT(--(INDEX(ilsort,,1)="chicago"),--(INDEX
(ilsort,,2)="cook")+(INDEX(ilsort,,2)="page"),INDE X
(ilsort,,4))

Of course, you need to use the actual criteria names in
place of my shortcuts!

OR, you can use cell references:

=SUMPRODUCT(--(INDEX(ilsort,,1)=A1),--(INDEX(ilsort,,2)
=B1)
+(INDEX(ilsort,,2)=C1),INDEX(ilsort,,4))

Biff

-----Original Message-----
Hello,

Can anyone help me with adding two index matches
together.

I have the following formula which searches for the
city
of chicago in the
county of cook if NOT true return zero if true return
column 4.

However I need the formula to search as above but also
search for Chicago in
Du Page county and return the total of both.

How can I accomplish this in a formula? Is it
possible?

=IF(ISNA(INDEX(ilsort,MATCH(1,(INDEX(ilsort,,1)
="Totals
for City:
CHICAGO")*(INDEX(ilsort,,2)=" County:
COOK"),0),4)),0,INDEX(ilsort,MATCH(1,(INDEX(ilsor t,,1)
="Totals for City:
CHICAGO")*(INDEX(ilsort,,2)=" County: COOK"),0),4))

Thanks for your help
.


.

Thank you. I just recently learned of this newgroup and I that is what I
have been doing. I go through questions print them, try them out and save
them in a special folder just for formulas to help me remember. This
Newgroup is extremely helpful.

Thanks so much!


"Biff" wrote:

Hi!

See this:

http://mcgimpsey.com/excel/formulae/doubleneg.html

The best way to learn, IMO, is to read the posts you find
here in the newsgroup. Read the questions, think of what
you would do for a solution, then read the replies to the
question. Books are OK, but very few books can replicate
the "real world" situations that you find in these
newsgroups.

I have stacks of books but 90% of what I have learned is
from right here in the newsgroups. There are some truly
brilliant people that answer posts. I wish I was one of
them!

Biff

-----Original Message-----
WOW, that worked great!. I'm beginning to dabble with
these intense
formulas can you tell me what the -- represent.

Even though I use the help feature to try and figures
things out at times I
just don't understand what or why a function would come
first when there are
multiple functions in a formula. Is there somewhere here
in KB search that
can explain this?

thank you so much for your help.



"Biff" wrote:

Hi!

Try this, it's much shorter:

=SUMPRODUCT(--(INDEX(ilsort,,1)="chicago"),--(INDEX
(ilsort,,2)="cook")+(INDEX(ilsort,,2)="page"),INDE X
(ilsort,,4))

Of course, you need to use the actual criteria names in
place of my shortcuts!

OR, you can use cell references:

=SUMPRODUCT(--(INDEX(ilsort,,1)=A1),--(INDEX(ilsort,,2)
=B1)
+(INDEX(ilsort,,2)=C1),INDEX(ilsort,,4))

Biff

-----Original Message-----
Hello,

Can anyone help me with adding two index matches
together.

I have the following formula which searches for the
city
of chicago in the
county of cook if NOT true return zero if true return
column 4.

However I need the formula to search as above but also
search for Chicago in
Du Page county and return the total of both.

How can I accomplish this in a formula? Is it
possible?

=IF(ISNA(INDEX(ilsort,MATCH(1,(INDEX(ilsort,,1)
="Totals
for City:
CHICAGO")*(INDEX(ilsort,,2)=" County:
COOK"),0),4)),0,INDEX(ilsort,MATCH(1,(INDEX(ilsor t,,1)
="Totals for City:
CHICAGO")*(INDEX(ilsort,,2)=" County: COOK"),0),4))

Thanks for your help
.


.