Excel – Why doesn’t formula work on COPIED data

copy/pastecountifmicrosoft-excel-2010

I'm using countif formulas across two and three columns in an Excel 2010 spreadsheet. The formulas work perfectly on data that is ENTERED but not on data that is COPIED into the spreadsheet, even from another Excel spreadsheet.

I've tried all possible pasting options and none works. I'm pasting into cells several rows above the working formulas (so no overwriting possible).

I have verified the sheet is calculating. I've even tried switching to manual, pasting, then pressing F9, but that makes no difference.

How can I resolve this?

Best Answer

When pasting the data into the spreadsheet containing the COUNTIFS formula, right click on the cell where you want to paste and select "Paste Special..." as in the screenshot below.

enter image description here

In the settings box, select "Values" and click OK (screenshot below). This will paste the copied values without changing the formatting of the destination cells.

enter image description here

If you are certain that the format of the destination cells is correct, go to the Data tab and click the "Refresh all" button.

enter image description here

Related Question