One Sumproduct Formula works - while other returns #VALUE!?

Received help with this one before (see Excel Formula - 5/3/2006) and now the
formula works great for the worksheet labelled "Correction Values". But now,
almost the exactly same scenerio with a worksheet labelled "Density Chart"
but I am getting a #VALUE! error.

Are my eyes just tired and I'm missing something?
=SUMPRODUCT(('Density Chart'!A3:A9427=B3)*('Density
Chart'!B3:B9427=B4)*('Density Chart'!D3:D9427))

If this range holds any text values it will return a value error,

'Density Chart'!D3:D9427

text values can be blanks from formulas like "" or plain text

you can rewrite it as


=SUMPRODUCT(--('Density Chart'!A3:A9427=B3),--('Density
Chart'!B3:B9427=B4),'Density Chart'!D3:D9427)


that will ignore text or fix the txt values in that range


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Correna" <[email protected]> wrote in message
news:[email protected]...
> Received help with this one before (see Excel Formula - 5/3/2006) and now
> the
> formula works great for the worksheet labelled "Correction Values". But
> now,
> almost the exactly same scenerio with a worksheet labelled "Density Chart"
> but I am getting a #VALUE! error.
>
> Are my eyes just tired and I'm missing something?
> =SUMPRODUCT(('Density Chart'!A3:A9427=B3)*('Density
> Chart'!B3:B9427=B4)*('Density Chart'!D3:D9427))
>
>

The only range that holds text values is 'Density Chart'!A3:A9427)
The other two are numerical values. I put in the -- as you stated below,
still getting the error.



"Peo Sjoblom" wrote:

> If this range holds any text values it will return a value error,
>
> 'Density Chart'!D3:D9427
>
> text values can be blanks from formulas like "" or plain text
>
> you can rewrite it as
>
>
> =SUMPRODUCT(--('Density Chart'!A3:A9427=B3),--('Density
> Chart'!B3:B9427=B4),'Density Chart'!D3:D9427)
>
>
> that will ignore text or fix the txt values in that range
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
> "Correna" <[email protected]> wrote in message
> news:[email protected]...
> > Received help with this one before (see Excel Formula - 5/3/2006) and now
> > the
> > formula works great for the worksheet labelled "Correction Values". But
> > now,
> > almost the exactly same scenerio with a worksheet labelled "Density Chart"
> > but I am getting a #VALUE! error.
> >
> > Are my eyes just tired and I'm missing something?
> > =SUMPRODUCT(('Density Chart'!A3:A9427=B3)*('Density
> > Chart'!B3:B9427=B4)*('Density Chart'!D3:D9427))
> >
> >
>
>
>

Check if you have an error within any of those ranges, that would also
result in an error
press F5, click special formulas and deselect everything but errors

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Correna" <[email protected]> wrote in message
news:[email protected]...
> The only range that holds text values is 'Density Chart'!A3:A9427)
> The other two are numerical values. I put in the -- as you stated below,
> still getting the error.
>
>
>
> "Peo Sjoblom" wrote:
>
>> If this range holds any text values it will return a value error,
>>
>> 'Density Chart'!D3:D9427
>>
>> text values can be blanks from formulas like "" or plain text
>>
>> you can rewrite it as
>>
>>
>> =SUMPRODUCT(--('Density Chart'!A3:A9427=B3),--('Density
>> Chart'!B3:B9427=B4),'Density Chart'!D3:D9427)
>>
>>
>> that will ignore text or fix the txt values in that range
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> http://nwexcelsolutions.com
>>
>>
>>
>> "Correna" <[email protected]> wrote in message
>> news:[email protected]...
>> > Received help with this one before (see Excel Formula - 5/3/2006) and
>> > now
>> > the
>> > formula works great for the worksheet labelled "Correction Values".
>> > But
>> > now,
>> > almost the exactly same scenerio with a worksheet labelled "Density
>> > Chart"
>> > but I am getting a #VALUE! error.
>> >
>> > Are my eyes just tired and I'm missing something?
>> > =SUMPRODUCT(('Density Chart'!A3:A9427=B3)*('Density
>> > Chart'!B3:B9427=B4)*('Density Chart'!D3:D9427))
>> >
>> >
>>
>>
>>

Would another formula work...
Currently the formula does not work, regardless if I change formats and/or
check for errors. This formula is causing the rest of the errors down the
line.

Trying to lookup in "Density Chart" column A if equal to B3, then lookup in
same chart column B if equal to B4, to return the value of cell D.

Does this help?

"Peo Sjoblom" wrote:

> Check if you have an error within any of those ranges, that would also
> result in an error
> press F5, click special formulas and deselect everything but errors
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Correna" <[email protected]> wrote in message
> news:[email protected]...
> > The only range that holds text values is 'Density Chart'!A3:A9427)
> > The other two are numerical values. I put in the -- as you stated below,
> > still getting the error.
> >
> >
> >
> > "Peo Sjoblom" wrote:
> >
> >> If this range holds any text values it will return a value error,
> >>
> >> 'Density Chart'!D3:D9427
> >>
> >> text values can be blanks from formulas like "" or plain text
> >>
> >> you can rewrite it as
> >>
> >>
> >> =SUMPRODUCT(--('Density Chart'!A3:A9427=B3),--('Density
> >> Chart'!B3:B9427=B4),'Density Chart'!D3:D9427)
> >>
> >>
> >> that will ignore text or fix the txt values in that range
> >>
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> http://nwexcelsolutions.com
> >>
> >>
> >>
> >> "Correna" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Received help with this one before (see Excel Formula - 5/3/2006) and
> >> > now
> >> > the
> >> > formula works great for the worksheet labelled "Correction Values".
> >> > But
> >> > now,
> >> > almost the exactly same scenerio with a worksheet labelled "Density
> >> > Chart"
> >> > but I am getting a #VALUE! error.
> >> >
> >> > Are my eyes just tired and I'm missing something?
> >> > =SUMPRODUCT(('Density Chart'!A3:A9427=B3)*('Density
> >> > Chart'!B3:B9427=B4)*('Density Chart'!D3:D9427))
> >> >
> >> >
> >>
> >>
> >>
>
>
>