Problem with a SUMPRODUCT Formula

[carl]

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.

 

[Morrigan]

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]

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

 

[daddylonglegs]

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)

 

[carl]

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

 

[carl]

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

If I send you a sample spreadsheet would it help ?

 

[Morrigan]

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

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

 

[carl]

And then what ? What am I looking for ?

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

 

[Morrigan]

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

And then what ? What am I looking for ?