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!

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!

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!

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!

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!

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!

>

>

>

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!

> >

> >

> >

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!

> > >

> > >

> > >

>

>

>