[SOLVED] Sumproduct with two criteria

[Rob]

I should be able to do this by now but I keep getting an #NA error. I am
trying to use this sumproduct formula
=SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
am looking up store #"921" and Attribute "fb and sum where these two criteria
meet. The column I want to sum is AS7:As285. What am I doing wrong.

[Bob Tarburton]

You are missing the "--" before the 1st condition and the "," before the
last

"Rob" <[email protected]> wrote in message
news:[email protected]...
>I should be able to do this by now but I keep getting an #NA error. I am
> trying to use this sumproduct formula
> =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where
> I
> am looking up store #"921" and Attribute "fb and sum where these two
> criteria
> meet. The column I want to sum is AS7:As285. What am I doing wrong.

[Sloth]

=SUMPRODUCT(--($I$7:$I$2585=921),--($L$7:$L$2585="Fb"),--(AS7:AS2585))

"Rob" wrote:

> I should be able to do this by now but I keep getting an #NA error. I am
> trying to use this sumproduct formula
> =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
> am looking up store #"921" and Attribute "fb and sum where these two criteria
> meet. The column I want to sum is AS7:As285. What am I doing wrong.

[Rob]

Thanks for the help but I am still getting an NA. I traced the error and
there is an NA in the I column but that does not meet my criteria.

"Sloth" wrote:

> =SUMPRODUCT(--($I$7:$I$2585=921),--($L$7:$L$2585="Fb"),--(AS7:AS2585))
>
> "Rob" wrote:
>
> > I should be able to do this by now but I keep getting an #NA error. I am
> > trying to use this sumproduct formula
> > =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
> > am looking up store #"921" and Attribute "fb and sum where these two criteria
> > meet. The column I want to sum is AS7:As285. What am I doing wrong.

[Kevin Vaughn]

I was going to say, I don't believe sumproduct will return an #N/A unless the
value is in the range to be summed as N/A is the result of a failed lookup.
You could account for the possibility in the sumproduct formula, but it is
probably better to take care of it at its source. Many people wrap their
lookup formula in a ISNA function .. =isna(vlookup( ...), "", vlookup( ...))
which basically says if the value returned from the vlookup is #N/A then
return Null else return the vlookup value.

I saw something on a related forum this morning that I thought was clever.
It was basically =if(countif(range, criteria) > 0, vlookup(criteria, range,
2, false) , 0)
Though I would probably use "" rather than 0 (for the value to be returned
if criteria not found.

The suggestion was from Jim ...(don't remember his last name, hold on.)
Thomlinson (if I'm not mistaken)
--
Kevin Vaughn


"Rob" wrote:

> Thanks for the help but I am still getting an NA. I traced the error and
> there is an NA in the I column but that does not meet my criteria.
>
> "Sloth" wrote:
>
> > =SUMPRODUCT(--($I$7:$I$2585=921),--($L$7:$L$2585="Fb"),--(AS7:AS2585))
> >
> > "Rob" wrote:
> >
> > > I should be able to do this by now but I keep getting an #NA error. I am
> > > trying to use this sumproduct formula
> > > =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
> > > am looking up store #"921" and Attribute "fb and sum where these two criteria
> > > meet. The column I want to sum is AS7:As285. What am I doing wrong.

[Rob]

Thanks Kevin:

How would I write that statement with two criteria. The thing about the NA
is that I was having a problem with my sumif statement with 1 criteria
returning an NA even though the NA was not a part of the criteria . I
resolved that one and didn't have to change the data. I just don't get this
one. (LOL)

"Kevin Vaughn" wrote:

> I was going to say, I don't believe sumproduct will return an #N/A unless the
> value is in the range to be summed as N/A is the result of a failed lookup.
> You could account for the possibility in the sumproduct formula, but it is
> probably better to take care of it at its source. Many people wrap their
> lookup formula in a ISNA function .. =isna(vlookup( ...), "", vlookup( ...))
> which basically says if the value returned from the vlookup is #N/A then
> return Null else return the vlookup value.
>
> I saw something on a related forum this morning that I thought was clever.
> It was basically =if(countif(range, criteria) > 0, vlookup(criteria, range,
> 2, false) , 0)
> Though I would probably use "" rather than 0 (for the value to be returned
> if criteria not found.
>
> The suggestion was from Jim ...(don't remember his last name, hold on.)
> Thomlinson (if I'm not mistaken)
> --
> Kevin Vaughn
>
>
> "Rob" wrote:
>
> > Thanks for the help but I am still getting an NA. I traced the error and
> > there is an NA in the I column but that does not meet my criteria.
> >
> > "Sloth" wrote:
> >
> > > =SUMPRODUCT(--($I$7:$I$2585=921),--($L$7:$L$2585="Fb"),--(AS7:AS2585))
> > >
> > > "Rob" wrote:
> > >
> > > > I should be able to do this by now but I keep getting an #NA error. I am
> > > > trying to use this sumproduct formula
> > > > =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
> > > > am looking up store #"921" and Attribute "fb and sum where these two criteria
> > > > meet. The column I want to sum is AS7:As285. What am I doing wrong.

[Kevin Vaughn]

Why don't you post the formula that is returning #N/A? Somebody here will
more than likely be able to figure out a way of wrapping it in a formula that
will suppress the message.
--
Kevin Vaughn


"Rob" wrote:

>
> Thanks Kevin:
>
> How would I write that statement with two criteria. The thing about the NA
> is that I was having a problem with my sumif statement with 1 criteria
> returning an NA even though the NA was not a part of the criteria . I
> resolved that one and didn't have to change the data. I just don't get this
> one. (LOL)
>
> "Kevin Vaughn" wrote:
>
> > I was going to say, I don't believe sumproduct will return an #N/A unless the
> > value is in the range to be summed as N/A is the result of a failed lookup.
> > You could account for the possibility in the sumproduct formula, but it is
> > probably better to take care of it at its source. Many people wrap their
> > lookup formula in a ISNA function .. =isna(vlookup( ...), "", vlookup( ...))
> > which basically says if the value returned from the vlookup is #N/A then
> > return Null else return the vlookup value.
> >
> > I saw something on a related forum this morning that I thought was clever.
> > It was basically =if(countif(range, criteria) > 0, vlookup(criteria, range,
> > 2, false) , 0)
> > Though I would probably use "" rather than 0 (for the value to be returned
> > if criteria not found.
> >
> > The suggestion was from Jim ...(don't remember his last name, hold on.)
> > Thomlinson (if I'm not mistaken)
> > --
> > Kevin Vaughn
> >
> >
> > "Rob" wrote:
> >
> > > Thanks for the help but I am still getting an NA. I traced the error and
> > > there is an NA in the I column but that does not meet my criteria.
> > >
> > > "Sloth" wrote:
> > >
> > > > =SUMPRODUCT(--($I$7:$I$2585=921),--($L$7:$L$2585="Fb"),--(AS7:AS2585))
> > > >
> > > > "Rob" wrote:
> > > >
> > > > > I should be able to do this by now but I keep getting an #NA error. I am
> > > > > trying to use this sumproduct formula
> > > > > =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
> > > > > am looking up store #"921" and Attribute "fb and sum where these two criteria
> > > > > meet. The column I want to sum is AS7:As285. What am I doing wrong.

[daddylonglegs]

As Kevin says, it's probably better to eliminate #N/A at source. Do you only have #N/A in column I, what formula do you have in I?

An alternative is to use a formula like

=SUM(IF(ISNUMBER($I$7:$I$2585),($I$7:$I$2585=921)*($L$7:$L$2585="Fb")*(AS7:AS2585)))

confirmed with CTRL+SHIFT+ENTER

[Bob Tarburton]

Whatever your formulaa are for populating columns I, L, and AS, just wrap
them inside an IF and ISERROR like this
=IF(ISERROR(your_formula),"",your_formula)
That should solve the problem

"Rob" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks Kevin:
>
> How would I write that statement with two criteria. The thing about the
> NA
> is that I was having a problem with my sumif statement with 1 criteria
> returning an NA even though the NA was not a part of the criteria . I
> resolved that one and didn't have to change the data. I just don't get
> this
> one. (LOL)
>
> "Kevin Vaughn" wrote:
>
>> I was going to say, I don't believe sumproduct will return an #N/A unless
>> the
>> value is in the range to be summed as N/A is the result of a failed
>> lookup.
>> You could account for the possibility in the sumproduct formula, but it
>> is
>> probably better to take care of it at its source. Many people wrap their
>> lookup formula in a ISNA function .. =isna(vlookup( ...), "", vlookup(
>> ...))
>> which basically says if the value returned from the vlookup is #N/A then
>> return Null else return the vlookup value.
>>
>> I saw something on a related forum this morning that I thought was
>> clever.
>> It was basically =if(countif(range, criteria) > 0, vlookup(criteria,
>> range,
>> 2, false) , 0)
>> Though I would probably use "" rather than 0 (for the value to be
>> returned
>> if criteria not found.
>>
>> The suggestion was from Jim ...(don't remember his last name, hold on.)
>> Thomlinson (if I'm not mistaken)
>> --
>> Kevin Vaughn
>>
>>
>> "Rob" wrote:
>>
>> > Thanks for the help but I am still getting an NA. I traced the error
>> > and
>> > there is an NA in the I column but that does not meet my criteria.
>> >
>> > "Sloth" wrote:
>> >
>> > > =SUMPRODUCT(--($I$7:$I$2585=921),--($L$7:$L$2585="Fb"),--(AS7:AS2585))
>> > >
>> > > "Rob" wrote:
>> > >
>> > > > I should be able to do this by now but I keep getting an #NA error.
>> > > > I am
>> > > > trying to use this sumproduct formula
>> > > > =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585))
>> > > > where I
>> > > > am looking up store #"921" and Attribute "fb and sum where these
>> > > > two criteria
>> > > > meet. The column I want to sum is AS7:As285. What am I doing
>> > > > wrong.

[R..VENKATARAMAN]

is there any #N/A in any of reference columns?


"Rob" <[email protected]> wrote in message
news:[email protected]...
>I should be able to do this by now but I keep getting an #NA error. I am
> trying to use this sumproduct formula
> =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where
> I
> am looking up store #"921" and Attribute "fb and sum where these two
> criteria
> meet. The column I want to sum is AS7:As285. What am I doing wrong.