# Sumproduct with #N/A in range

1. ## Sumproduct with #N/A in range

I have a column B of names that is part of my sumproduct formula...there are
some cells in this column that contain #N/A. I have to be able to complete
the sumproduct formula by ignoring these errors... Is there something I can
add to the sumproduct formula to ignore these errors in column B?

Thanks again!

2. ## RE: Sumproduct with #N/A in range

Maybe something like this:

=SUM(IF(\$A\$21:\$A\$36=11,IF(NOT(ISNA(\$B\$21:\$B\$36)),\$B21:B36,0)))
which is an array formula (enter using cntl-shift-enter instead of just enter)

--
Kevin Vaughn

"Deeds" wrote:

> I have a column B of names that is part of my sumproduct formula...there are
> some cells in this column that contain #N/A. I have to be able to complete
> the sumproduct formula by ignoring these errors... Is there something I can
> add to the sumproduct formula to ignore these errors in column B?
>
> Thanks again!

3. ## RE: Sumproduct with #N/A in range

Let me give you my formula:
=sumproduct(B2:B400)*(A2:A400=F1)

With the above....when it finds an #N/A in Column A...it returns #N/A. I
need to add something to that section to ignore any #N/A.

Thanks again.

"Kevin Vaughn" wrote:

> Maybe something like this:
>
> =SUM(IF(\$A\$21:\$A\$36=11,IF(NOT(ISNA(\$B\$21:\$B\$36)),\$B21:B36,0)))
> which is an array formula (enter using cntl-shift-enter instead of just enter)
>
> --
> Kevin Vaughn
>
>
> "Deeds" wrote:
>
> > I have a column B of names that is part of my sumproduct formula...there are
> > some cells in this column that contain #N/A. I have to be able to complete
> > the sumproduct formula by ignoring these errors... Is there something I can
> > add to the sumproduct formula to ignore these errors in column B?
> >
> > Thanks again!

4. ## Re: Sumproduct with #N/A in range

You don't need SUMPRODUCT

=SUMIF(A2:A400,F1,B2:B400)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Deeds" <Deeds@discussions.microsoft.com> wrote in message
news:83B6AB7B-48C2-44FA-8854-97F469D4518F@microsoft.com...
> Let me give you my formula:
> =sumproduct(B2:B400)*(A2:A400=F1)
>
> With the above....when it finds an #N/A in Column A...it returns #N/A. I
> need to add something to that section to ignore any #N/A.
>
> Thanks again.
>
> "Kevin Vaughn" wrote:
>
> > Maybe something like this:
> >
> > =SUM(IF(\$A\$21:\$A\$36=11,IF(NOT(ISNA(\$B\$21:\$B\$36)),\$B21:B36,0)))
> > which is an array formula (enter using cntl-shift-enter instead of just

enter)
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Deeds" wrote:
> >
> > > I have a column B of names that is part of my sumproduct

formula...there are
> > > some cells in this column that contain #N/A. I have to be able to

complete
> > > the sumproduct formula by ignoring these errors... Is there something

I can
> > > add to the sumproduct formula to ignore these errors in column B?
> > >
> > > Thanks again!

5. ## RE: Sumproduct with #N/A in range

Applying my formula to your range (and borrowing from Bob's answer,) I came
up with:

=SUM(IF(A2:A400=F1,IF(NOT(ISNA(B2:B400)),B2:B400,0)))
which again is an array entered formula (ctrl-shift-enter)

--
Kevin Vaughn

"Deeds" wrote:

> Let me give you my formula:
> =sumproduct(B2:B400)*(A2:A400=F1)
>
> With the above....when it finds an #N/A in Column A...it returns #N/A. I
> need to add something to that section to ignore any #N/A.
>
> Thanks again.
>
> "Kevin Vaughn" wrote:
>
> > Maybe something like this:
> >
> > =SUM(IF(\$A\$21:\$A\$36=11,IF(NOT(ISNA(\$B\$21:\$B\$36)),\$B21:B36,0)))
> > which is an array formula (enter using cntl-shift-enter instead of just enter)
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Deeds" wrote:
> >
> > > I have a column B of names that is part of my sumproduct formula...there are
> > > some cells in this column that contain #N/A. I have to be able to complete
> > > the sumproduct formula by ignoring these errors... Is there something I can
> > > add to the sumproduct formula to ignore these errors in column B?
> > >
> > > Thanks again!

6. ## Re: Sumproduct with #N/A in range

Sorry for the confusion....the referenced formula is a stripped down version.
I do have other criteria in the formula. I was trying to show just the part
that is giving me errors. Bottom line is that I am trying to find something
that I can add to my sumproduct formula that ignores #N/A.
Thanks again!

"Bob Phillips" wrote:

> You don't need SUMPRODUCT
>
> =SUMIF(A2:A400,F1,B2:B400)
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Deeds" <Deeds@discussions.microsoft.com> wrote in message
> news:83B6AB7B-48C2-44FA-8854-97F469D4518F@microsoft.com...
> > Let me give you my formula:
> > =sumproduct(B2:B400)*(A2:A400=F1)
> >
> > With the above....when it finds an #N/A in Column A...it returns #N/A. I
> > need to add something to that section to ignore any #N/A.
> >
> > Thanks again.
> >
> > "Kevin Vaughn" wrote:
> >
> > > Maybe something like this:
> > >
> > > =SUM(IF(\$A\$21:\$A\$36=11,IF(NOT(ISNA(\$B\$21:\$B\$36)),\$B21:B36,0)))
> > > which is an array formula (enter using cntl-shift-enter instead of just

> enter)
> > >
> > > --
> > > Kevin Vaughn
> > >
> > >
> > > "Deeds" wrote:
> > >
> > > > I have a column B of names that is part of my sumproduct

> formula...there are
> > > > some cells in this column that contain #N/A. I have to be able to

> complete
> > > > the sumproduct formula by ignoring these errors... Is there something

> I can
> > > > add to the sumproduct formula to ignore these errors in column B?
> > > >
> > > > Thanks again!

>
>
>

7. ## Re: Sumproduct with #N/A in range

Then use the same technique I showed you earlier

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A400,F1,0))),B2:B400)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Deeds" <Deeds@discussions.microsoft.com> wrote in message
news:2BD7844D-BF15-44E9-8891-CA45EE6E63E9@microsoft.com...
> Sorry for the confusion....the referenced formula is a stripped down

version.
> I do have other criteria in the formula. I was trying to show just the

part
> that is giving me errors. Bottom line is that I am trying to find

something
> that I can add to my sumproduct formula that ignores #N/A.
> Thanks again!
>
> "Bob Phillips" wrote:
>
> > You don't need SUMPRODUCT
> >
> > =SUMIF(A2:A400,F1,B2:B400)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Deeds" <Deeds@discussions.microsoft.com> wrote in message
> > news:83B6AB7B-48C2-44FA-8854-97F469D4518F@microsoft.com...
> > > Let me give you my formula:
> > > =sumproduct(B2:B400)*(A2:A400=F1)
> > >
> > > With the above....when it finds an #N/A in Column A...it returns #N/A.

I
> > > need to add something to that section to ignore any #N/A.
> > >
> > > Thanks again.
> > >
> > > "Kevin Vaughn" wrote:
> > >
> > > > Maybe something like this:
> > > >
> > > > =SUM(IF(\$A\$21:\$A\$36=11,IF(NOT(ISNA(\$B\$21:\$B\$36)),\$B21:B36,0)))
> > > > which is an array formula (enter using cntl-shift-enter instead of

just
> > enter)
> > > >
> > > > --
> > > > Kevin Vaughn
> > > >
> > > >
> > > > "Deeds" wrote:
> > > >
> > > > > I have a column B of names that is part of my sumproduct

> > formula...there are
> > > > > some cells in this column that contain #N/A. I have to be able to

> > complete
> > > > > the sumproduct formula by ignoring these errors... Is there

something
> > I can
> > > > > add to the sumproduct formula to ignore these errors in column B?
> > > > >
> > > > > Thanks again!

> >
> >
> >

8. ## Re: Sumproduct with #N/A in range

right in front of me....sorry. Much appreciated!

"Bob Phillips" wrote:

> Then use the same technique I showed you earlier
>
> =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A400,F1,0))),B2:B400)
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Deeds" <Deeds@discussions.microsoft.com> wrote in message
> news:2BD7844D-BF15-44E9-8891-CA45EE6E63E9@microsoft.com...
> > Sorry for the confusion....the referenced formula is a stripped down

> version.
> > I do have other criteria in the formula. I was trying to show just the

> part
> > that is giving me errors. Bottom line is that I am trying to find

> something
> > that I can add to my sumproduct formula that ignores #N/A.
> > Thanks again!
> >
> > "Bob Phillips" wrote:
> >
> > > You don't need SUMPRODUCT
> > >
> > > =SUMIF(A2:A400,F1,B2:B400)
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove xxx from email address if mailing direct)
> > >
> > > "Deeds" <Deeds@discussions.microsoft.com> wrote in message
> > > news:83B6AB7B-48C2-44FA-8854-97F469D4518F@microsoft.com...
> > > > Let me give you my formula:
> > > > =sumproduct(B2:B400)*(A2:A400=F1)
> > > >
> > > > With the above....when it finds an #N/A in Column A...it returns #N/A.

> I
> > > > need to add something to that section to ignore any #N/A.
> > > >
> > > > Thanks again.
> > > >
> > > > "Kevin Vaughn" wrote:
> > > >
> > > > > Maybe something like this:
> > > > >
> > > > > =SUM(IF(\$A\$21:\$A\$36=11,IF(NOT(ISNA(\$B\$21:\$B\$36)),\$B21:B36,0)))
> > > > > which is an array formula (enter using cntl-shift-enter instead of

> just
> > > enter)
> > > > >
> > > > > --
> > > > > Kevin Vaughn
> > > > >
> > > > >
> > > > > "Deeds" wrote:
> > > > >
> > > > > > I have a column B of names that is part of my sumproduct
> > > formula...there are
> > > > > > some cells in this column that contain #N/A. I have to be able to
> > > complete
> > > > > > the sumproduct formula by ignoring these errors... Is there

> something
> > > I can
> > > > > > add to the sumproduct formula to ignore these errors in column B?
> > > > > >
> > > > > > Thanks again!
> > >
> > >
> > >

>
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1