+ Reply to Thread
Results 1 to 8 of 8

Sumproduct with #N/A in range

  1. #1
    Deeds
    Guest

    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. #2
    Kevin Vaughn
    Guest

    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. #3
    Deeds
    Guest

    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. #4
    Bob Phillips
    Guest

    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. #5
    Kevin Vaughn
    Guest

    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. #6
    Deeds
    Guest

    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. #7
    Bob Phillips
    Guest

    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. #8
    Deeds
    Guest

    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!
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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