Multiple Offset/Match formula returns "#N/A"

J

Jim McC

I have the following formula
=-OFFSET(INPUT!$A$1,MATCH(April!$I$8,INPUT!$A$2:$A$39,0),MATCH(April!A18,INPUT!$B$1:$AF$1,0))+OFFSET(INPUT!$A$1,MATCH(April!$I$9,INPUT!$A$2:$A$39,0),MATCH(April!A18,INPUT!$B$1:$AF$1,0))+OFFSET(INPUT!$A$1,MATCH(April!$I$11,INPUT!$A$2:$A$39,0),MATCH(April!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")?
 
T

T. Valko

Put each segment in an individual cell then use a SUMIF on those cells.

=SUMIF(A1:A3,"<1E100")
 
J

Jim McC

My solution is as follows (Using IF(ISNA
=IF(ISNA(-OFFSET(INPUT!$A$1,MATCH(April!$I$8,INPUT!$A$2:$A$39,0),MATCH(April!A16,INPUT!$B$1:$AF$1,0))),0,-OFFSET(INPUT!$A$1,MATCH(April!$I$8,INPUT!$A$2:$A$39,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,OFFSET(INPUT!$A$1,MATCH(April!$I$9,INPUT!$A$2:$A$39,0),MATCH(April!A16,INPUT!$B$1:$AF$1,0)))+IF(ISNA(OFFSET(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),MATCH(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)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top