Keep getting #N/A versus Dollar amts
 

I've used Sumproduct extensively over the years, but now I'm trying another
routine type set up and it is returning the infamous #N/A in my cell.
grrrrrrr

My Column H is a fromula that returns either: "Reg", "VsMc" or "Disc" <<
w/o quotes

My Column L is a formula that returns a number - formatted as text ( say
4115)
Cell $K$3 represent the same 4 digit number that is formatted as Text

My Column I is strictly Dollar-amts.

When I test Column E, H and I on various cells, like =ISTEXT() they all
return TRUE

Help -- please...

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),$I$8:$I$4541)

Keep getting #N/A versus Dollar amts
 

Did you try

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),($I$8:$I$4541))


"JMay" wrote:

I've used Sumproduct extensively over the years, but now I'm trying another
routine type set up and it is returning the infamous #N/A in my cell.
grrrrrrr

My Column H is a fromula that returns either: "Reg", "VsMc" or "Disc" <<
w/o quotes

My Column L is a formula that returns a number - formatted as text ( say
4115)
Cell $K$3 represent the same 4 digit number that is formatted as Text

My Column I is strictly Dollar-amts.

When I test Column E, H and I on various cells, like =ISTEXT() they all
return TRUE

Help -- please...

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),$I$8:$I$4541)

Keep getting #N/A versus Dollar amts
 

Discovered deep down in my rows (between 8 and 4531) I have a couple of
#N/A's in the cells of Column I << pluse I had a couple numbers in Column L
that were not
TEXT, Fould using the Goto Specialbox formulas TEXT (only)..

Thanks for the suggestion.

Jim

"Barb Reinhardt" wrote:

Did you try

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),($I$8:$I$4541))


"JMay" wrote:

I've used Sumproduct extensively over the years, but now I'm trying another
routine type set up and it is returning the infamous #N/A in my cell.
grrrrrrr

My Column H is a fromula that returns either: "Reg", "VsMc" or "Disc" <<
w/o quotes

My Column L is a formula that returns a number - formatted as text ( say
4115)
Cell $K$3 represent the same 4 digit number that is formatted as Text

My Column I is strictly Dollar-amts.

When I test Column E, H and I on various cells, like =ISTEXT() they all
return TRUE

Help -- please...

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),$I$8:$I$4541)

Keep getting #N/A versus Dollar amts
 

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),(if(isnumber($I$8:$I$4541),$I$ 8:$I$4541))

Might work in that case.


"JMay" wrote:

Discovered deep down in my rows (between 8 and 4531) I have a couple of
#N/A's in the cells of Column I << pluse I had a couple numbers in Column L
that were not
TEXT, Fould using the Goto Specialbox formulas TEXT (only)..

Thanks for the suggestion.

Jim

"Barb Reinhardt" wrote:

Did you try

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),($I$8:$I$4541))


"JMay" wrote:

I've used Sumproduct extensively over the years, but now I'm trying another
routine type set up and it is returning the infamous #N/A in my cell.
grrrrrrr

My Column H is a fromula that returns either: "Reg", "VsMc" or "Disc" <<
w/o quotes

My Column L is a formula that returns a number - formatted as text ( say
4115)
Cell $K$3 represent the same 4 digit number that is formatted as Text

My Column I is strictly Dollar-amts.

When I test Column E, H and I on various cells, like =ISTEXT() they all
return TRUE

Help -- please...

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),$I$8:$I$4541)

Keep getting #N/A versus Dollar amts
 

Correct the #N/A and then try this

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--(--$L$8:$L$4541=--$K$3),$I$8:$I$4541)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Barb Reinhardt" wrote in message
...
=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),(if(isnumber($I$8:$I$4541),$I$ 8:$I$4541))

Might work in that case.


"JMay" wrote:

Discovered deep down in my rows (between 8 and 4531) I have a couple of
#N/A's in the cells of Column I << pluse I had a couple numbers in Column
L
that were not
TEXT, Fould using the Goto Specialbox formulas TEXT (only)..

Thanks for the suggestion.

Jim

"Barb Reinhardt" wrote:

Did you try

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),($I$8:$I$4541))


"JMay" wrote:

I've used Sumproduct extensively over the years, but now I'm trying
another
routine type set up and it is returning the infamous #N/A in my
cell.
grrrrrrr

My Column H is a fromula that returns either: "Reg", "VsMc" or
"Disc" <<
w/o quotes

My Column L is a formula that returns a number - formatted as text
( say
4115)
Cell $K$3 represent the same 4 digit number that is formatted as Text

My Column I is strictly Dollar-amts.

When I test Column E, H and I on various cells, like =ISTEXT()
they all
return TRUE

Help -- please...

=SUMPRODUCT(--($H$8:$H$4541="VsMc"),--($L$8:$L$4541=$K$3),$I$8:$I$4541)