Changing a range of an array in a SUMPRODUCT formula gives a #N/A error

I have a working SUMPRODUCT formula
{=SUMPRODUCT(($C$4:$C$4341=z8)*(audittypefm1="Standard"))} that I need
to change the 1st array to C5623. When I do I get a #N/A error. I've
tried giving the range a name (empnofm2), and I've verified all the
data in the C4:C5623 range is formated the same (general, it is all
employee numbers from 2 to 5 digits long. I'm using a "trim all" macro
which is working great. I am unable to determine what is causing the
#N/A and how to fix it. Basically I copied an entire worksheet within
the same workbook and changed the name, which I will continue to do for
each fiscal month. I then import the new FM data from Access and make
any necessary changes to formulas and range names to ensure totals are
accurate.. This is the only part that's not working.

HELP

Thanks
Chrism

Is it possible that the two ranges are not of the same size??

--
Don Guillett
SalesAid Software
donaldb@281.com
"Chrism" <cmoreau@llbean.com> wrote in message
news:1115211945.908317.4140@o13g2000cwo.googlegroups.com...
> I have a working SUMPRODUCT formula
> {=SUMPRODUCT(($C$4:$C$4341=z8)*(audittypefm1="Standard"))} that I need
> to change the 1st array to C5623. When I do I get a #N/A error. I've
> tried giving the range a name (empnofm2), and I've verified all the
> data in the C4:C5623 range is formated the same (general, it is all
> employee numbers from 2 to 5 digits long. I'm using a "trim all" macro
> which is working great. I am unable to determine what is causing the
> #N/A and how to fix it. Basically I copied an entire worksheet within
> the same workbook and changed the name, which I will continue to do for
> each fiscal month. I then import the new FM data from Access and make
> any necessary changes to formulas and range names to ensure totals are
> accurate.. This is the only part that's not working.
>
> HELP
>
> Thanks
> Chrism
>

EXACTLY!
I named the ranges so missed that they referred to differently sized
ranges.

Again-I go this newsgroup and get a great & fast answer.

THANKS

glad to help

--
Don Guillett
SalesAid Software
donaldb@281.com
"Chrism" <cmoreau@llbean.com> wrote in message
news:1115217607.687862.189990@z14g2000cwz.googlegroups.com...
> EXACTLY!
> I named the ranges so missed that they referred to differently sized
> ranges.
>
> Again-I go this newsgroup and get a great & fast answer.
>
> THANKS
>

did you also change the range for "audittypefm1" to have the same number of
cells?
arrays in sumproduct have to be the same size

"Chrism" wrote:

> I have a working SUMPRODUCT formula
> {=SUMPRODUCT(($C$4:$C$4341=z8)*(audittypefm1="Standard"))} that I need
> to change the 1st array to C5623. When I do I get a #N/A error. I've
> tried giving the range a name (empnofm2), and I've verified all the
> data in the C4:C5623 range is formated the same (general, it is all
> employee numbers from 2 to 5 digits long. I'm using a "trim all" macro
> which is working great. I am unable to determine what is causing the
> #N/A and how to fix it. Basically I copied an entire worksheet within
> the same workbook and changed the name, which I will continue to do for
> each fiscal month. I then import the new FM data from Access and make
> any necessary changes to formulas and range names to ensure totals are
> accurate.. This is the only part that's not working.
>
> HELP
>
> Thanks
> Chrism
>
>