| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 . |
|
#3
|
|||
|
|||
|
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 . |
|
#4
|
|||
|
|||
|
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 . . |
|
#5
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array Formula | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
| Min formula not returning value from Index | Excel Worksheet Functions | |||
| Help with array formula | Excel Worksheet Functions | |||
| Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Linear Mode
