Problem with a SUMPRODUCT Formula

This formula works:

=SUMPRODUCT(--(AMS!$C$4:$C$60000="InitialOrderEntry");--(AMS!$Z$4:AMS!$Z$60000=17);--(AMS!$AB$4:$AB$60000=226);--(AMS!$AS$4:$AS$60000=$A$1);--(AMS!$J$4:$J$60000))

When I add this condition: (AMS!$B$4:$B$60000=Summary!C3)

=SUMPRODUCT(--(AMS!$C$4:$C$60000="InitialOrderEntry");--(AMS!$B$4:$B$60000=Summary!C3);--(AMS!$Z$4:AMS!$Z$60000=17);--(AMS!$AB$4:$AB$60000=226);--(AMS!$AS$4:$AS$60000=$A$1);--(AMS!$J$4:$J$60000))

The formula returns "0" - which is incorrect. The data in AMS!$B$4:$B$60000
looks like this - 20060314. The criteria in Summary!C3 is copied from
AMS!$B$4:$B$60000.

What am I doing wrong ?

Thank you in advance.

I am guessing this is one of those instants where Test to Columns may help. Apply Data -> Test to columns -> Finish to the 2 columns Summary!C and AMS!B.

What do you have in AMS!$B$4:$B$60000, are they dates? If so try

(TEXT(AMS!$B$4:$B$60000,"yyyymmdd")=Summary!C3)

Thank you. Not sure what you are refering to. can you please elaborate ?

"Morrigan" wrote:

>
> I am guessing this is one of those instants where Test to Columns may
> help. Apply Data -> Test to columns -> Finish to the 2 columns
> Summary!C and AMS!B.
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=524394
>
>

The data in both fields is the same - I think...

If I send you a sample spreadsheet would it help ?



"daddylonglegs" wrote:

>
> What do you have in AMS!$B$4:$B$60000, are they dates? If so try
>
> (TEXT(AMS!$B$4:$B$60000,"yyyymmdd")=Summary!C3)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=524394
>
>

Originally Posted by carl

Thank you. Not sure what you are refering to. can you please elaborate ?

"Morrigan" wrote:

>
> I am guessing this is one of those instants where Test to Columns may
> help. Apply Data -> Test to columns -> Finish to the 2 columns
> Summary!C and AMS!B.
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=524394
>
>

Highlight the 2 columns, go to Data -> Text to columns -> click Finish

And then what ? What am I looking for ?

"Morrigan" wrote:

>
> carl Wrote:
> > Thank you. Not sure what you are refering to. can you please elaborate
> > ?
> >
> > "Morrigan" wrote:
> >
> > >
> > > I am guessing this is one of those instants where Test to Columns
> > may
> > > help. Apply Data -> Test to columns -> Finish to the 2 columns
> > > Summary!C and AMS!B.
> > >
> > >
> > > --
> > > Morrigan
> > >
> > ------------------------------------------------------------------------
> > > Morrigan's Profile:
> > http://www.excelforum.com/member.php...fo&userid=7094
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=524394
> > >
> > >
>
>
>
> Highlight the 2 columns, go to Data -> Text to columns -> click Finish
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=524394
>
>

Hmm.....that's it. If that didn't fix it, it's probably not due to the misinterpretation of copy and paste from other sources.

Originally Posted by carl

And then what ? What am I looking for ?

"Morrigan" wrote:

>
> carl Wrote:
> > Thank you. Not sure what you are refering to. can you please elaborate
> > ?
> >
> > "Morrigan" wrote:
> >
> > >
> > > I am guessing this is one of those instants where Test to Columns
> > may
> > > help. Apply Data -> Test to columns -> Finish to the 2 columns
> > > Summary!C and AMS!B.
> > >
> > >
> > > --
> > > Morrigan
> > >
> > ------------------------------------------------------------------------
> > > Morrigan's Profile:
> > http://www.excelforum.com/member.php...fo&userid=7094
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=524394
> > >
> > >
>
>
>
> Highlight the 2 columns, go to Data -> Text to columns -> click Finish
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=524394
>
>