Multiple Offset/Match formula returns "#N/A"
I have the following formula:
=-OFFSET(INPUT!$A$1,MATCH(April!$I$8,INPUT!$A$2:$A$3 9,0),MATCH(April!A18,INPUT!$B$1:$AF$1,0))+OFFSET(I NPUT!$A$1,MATCH(April!$I$9,INPUT!$A$2:$A$39,0),MAT CH(April!A18,INPUT!$B$1:$AF$1,0))+OFFSET(INPUT!$A$ 1,MATCH(April!$I$11,INPUT!$A$2:$A$39,0),MATCH(Apri l!A18,INPUT!$B$1:$AF$1,0)) If one of the Offset/Matches returns "#N/A", then the entire formula is "#N/A". How can I fix it that if one (or two or three) returns "#N/A", I still get the sum of the other two (or one or "0")? |
Multiple Offset/Match formula returns "#N/A"
Put each segment in an individual cell then use a SUMIF on those cells.
=SUMIF(A1:A3,"<1E100") -- Biff Microsoft Excel MVP "Jim McC" wrote in message ... I have the following formula: =-OFFSET(INPUT!$A$1,MATCH(April!$I$8,INPUT!$A$2:$A$3 9,0),MATCH(April!A18,INPUT!$B$1:$AF$1,0))+OFFSET(I NPUT!$A$1,MATCH(April!$I$9,INPUT!$A$2:$A$39,0),MAT CH(April!A18,INPUT!$B$1:$AF$1,0))+OFFSET(INPUT!$A$ 1,MATCH(April!$I$11,INPUT!$A$2:$A$39,0),MATCH(Apri l!A18,INPUT!$B$1:$AF$1,0)) If one of the Offset/Matches returns "#N/A", then the entire formula is "#N/A". How can I fix it that if one (or two or three) returns "#N/A", I still get the sum of the other two (or one or "0")? |
Multiple Offset/Match formula returns "#N/A"
My solution is as follows (Using IF(ISNA)
=IF(ISNA(-OFFSET(INPUT!$A$1,MATCH(April!$I$8,INPUT!$A$2:$A$3 9,0),MATCH(April!A16,INPUT!$B$1:$AF$1,0))),0,-OFFSET(INPUT!$A$1,MATCH(April!$I$8,INPUT!$A$2:$A$3 9,0),MATCH(April!A16,INPUT!$B$1:$AF$1,0)))+IF(ISNA (OFFSET(INPUT!$A$1,MATCH(April!$I$9,INPUT!$A$2:$A$ 39,0),MATCH(April!A16,INPUT!$B$1:$AF$1,0))),0,OFFS ET(INPUT!$A$1,MATCH(April!$I$9,INPUT!$A$2:$A$39,0) ,MATCH(April!A16,INPUT!$B$1:$AF$1,0)))+IF(ISNA(OFF SET(INPUT!$A$1,MATCH(April!$I$11,INPUT!$A$2:$A$39, 0),MATCH(April!A16,INPUT!$B$1:$AF$1,0))),0,OFFSET( INPUT!$A$1,MATCH(April!$I$11,INPUT!$A$2:$A$39,0),M ATCH(April!A16,INPUT!$B$1:$AF$1,0)))-IF(ISNA(OFFSET(INPUT!$A$1,MATCH(April!$I$10,INPUT! $A$2:$A$39,0),MATCH(April!A16,INPUT!$B$1:$AF$1,0)) ),0,OFFSET(INPUT!$A$1,MATCH(April!$I$10,INPUT!$A$2 :$A$39,0),MATCH(April!A16,INPUT!$B$1:$AF$1,0))) "Jim McC" wrote: I have the following formula: =-OFFSET(INPUT!$A$1,MATCH(April!$I$8,INPUT!$A$2:$A$3 9,0),MATCH(April!A18,INPUT!$B$1:$AF$1,0))+OFFSET(I NPUT!$A$1,MATCH(April!$I$9,INPUT!$A$2:$A$39,0),MAT CH(April!A18,INPUT!$B$1:$AF$1,0))+OFFSET(INPUT!$A$ 1,MATCH(April!$I$11,INPUT!$A$2:$A$39,0),MATCH(Apri l!A18,INPUT!$B$1:$AF$1,0)) If one of the Offset/Matches returns "#N/A", then the entire formula is "#N/A". How can I fix it that if one (or two or three) returns "#N/A", I still get the sum of the other two (or one or "0")? |
All times are GMT +1. The time now is 11:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com