Worksheet formula incorrect after copying to another worksheet
 

I have a formula in one workbook :

=COUNT(IF((Sheet1!$B$2:$B$180=DATEVALUE("Jan-09"))*(Sheet1!$N$2:$N$180=1),Sheet1!$A$2:$A$180) )

When i copied this formula into another workbook, the answer becomes "1".
The answer in the original worksheet is "19".

How can I get this corrected? Thanks

Worksheet formula incorrect after copying to another worksheet
 

--In edit mode F2, paste the formula to a cell and instead of just Enter
press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

--You can use the below alternative (enter normally)

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$180,"mmyy")="0109 ")*
(Sheet1!$N$2:$N$180=1))


If this post helps click Yes
---------------
Jacob Skaria


"Karen" wrote:

I have a formula in one workbook :

=COUNT(IF((Sheet1!$B$2:$B$180=DATEVALUE("Jan-09"))*(Sheet1!$N$2:$N$180=1),Sheet1!$A$2:$A$180) )

When i copied this formula into another workbook, the answer becomes "1".
The answer in the original worksheet is "19".

How can I get this corrected? Thanks

Worksheet formula incorrect after copying to another worksheet
 

Sorry, I clicked the "no" button wrongly. Actually its yes and no.

1. I tried pressing CTRL+SHIFT+ENTER and saw the curly braces at both ends
of the formula but it doesn't work. So I tried the other option.

2.
=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$180,"mmyy")="0109 ")*(Sheet1!$N$2:$N$180=1))
works!! But when i enter the mmyy as "1009", it reflected "0" which is
incorrect.

It there something wrong with the "mmyy" function?

Thank you again.


"Jacob Skaria" wrote:

--In edit mode F2, paste the formula to a cell and instead of just Enter
press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

--You can use the below alternative (enter normally)

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$180,"mmyy")="0109 ")*
(Sheet1!$N$2:$N$180=1))


If this post helps click Yes
---------------
Jacob Skaria


"Karen" wrote:

I have a formula in one workbook :

=COUNT(IF((Sheet1!$B$2:$B$180=DATEVALUE("Jan-09"))*(Sheet1!$N$2:$N$180=1),Sheet1!$A$2:$A$180) )

When i copied this formula into another workbook, the answer becomes "1".
The answer in the original worksheet is "19".

How can I get this corrected? Thanks