0

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?

Improve this question
11
  • 1
    Couple things we need to know; When pasting the data, are you pasting into the cells where your working formula exists (you might be overwriting your formulas)? Are you sure the sheet is calculating? If not, what happens if you press F9 after you paste the data?
    – CharlieRB
    Commented Jun 30, 2014 at 19:09
  • Pasting into cells several rows above the working formulas (so no overwriting possible). Yes, sheet is calculating. (Tried switching to manual, pasting, then pressing F9, but that makes no difference, either.)
    – Gail
    Commented Jul 1, 2014 at 14:31
  • Can you give us an example of the formula? Can you verify the cell references are staying intact? After pasting, does #REF appear in the formula in place of cell references?
    – CharlieRB
    Commented Jul 1, 2014 at 14:37
  • a typical formula: =COUNTIFS($C$8:$C$105,"=M",$D$8:$D$105,"=A")+COUNTIFS($C$8:$C$105,"=M",$D$8:$D$105,"=B")+COUNTIFS($C$8:$C$105,"=M",$D$8:$D$105,"=R"). Yes, cell refs stay intact & no #REFs appear. Reminder: this works perfectly when data is ENTERED in the target cells, only when data is COPIED does it not work (so I don't believe that it's the formula that's causing the problem).
    – Gail
    Commented Jul 1, 2014 at 14:58
  • @Gail Are you pasting data beyond row 105?
    – PFitz
    Commented Jul 1, 2014 at 15:03

5 Answers 5

Reset to default
2

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

Improve this answer
4
  • Would clicking the "123" icon beneath paste options not achieve the same result (pasting values only)?
    – WillemToorenburgh
    Commented Jun 30, 2014 at 18:14
  • @TorpedoBench Yes. That is correct but those icons are not present in older versions of Excel and I don't know what version you're working with.
    – PFitz
    Commented Jun 30, 2014 at 18:17
  • Using Excel 2010 -- and yes, Peter's steps above accomplishes the same thing as clicking the "123" icon. But it doesn't work, either way it's entered.
    – Gail
    Commented Jul 1, 2014 at 14:53
  • @Gail And the "Refresh All" button in the Data tab isn't doing the trick either? There are many possible causes for this issue so we'll need some more info to narrow it down. Take a look at CharlieRB's questions above (third comment on the original question)...
    – PFitz
    Commented Jul 1, 2014 at 15:00
0

You need to indicate to Excel that the reference to the cell in the formula is Absolute. Do this by inserting a dollar sign before the column $A and before the $1 in the formula. The formula in the cell would change to =($A$1). As you copy this formula down to other rows in your dataset, the portion that refers to $A$1 will continue to point at $A$1,

Improve this answer
0

https://support.office.com/en-us/article/Remove-spaces-and-nonprinting-characters-from-text-023f3a08-3d56-49e4-bf0c-fe5303222c9d

this fixed the problem for me.

Your numbers have one of the sticky-spaces after them (ASCII 160)

Improve this answer
2
  • 1
    External links can break or be unavailable, in which case your answer would not be useful. Even while links still work, the content can't be indexed to help people find the solutions, and the site's concept is to have answers here rather than redirect people elsewhere. Please include the essential information within your answer and use the link for attribution and further reading. Thanks.
    – fixer1234
    Commented Jan 16, 2017 at 20:37
  • 1
    Please quote the essential parts of the answer from the reference link(s), as the answer can become invalid if the linked page(s) change.
    – DavidPostill ♦
    Commented Jan 16, 2017 at 23:21
0

I know this is an old post but I wanted to share my experience with this since I just solved it for myself. Double check the data that you're copying/pasting. In my case, the copied data included a space after the value when I pasted it. Once I removed the space, my formula worked correctly.

Also, since my copied data is coming from an internet page, I have to paste it into notepad first (my guess is this removes the internet formatting) and then into my spreadsheet. It's an added step but I prefer it to entering the data myself one by one.

Hope that helps!

Improve this answer
0

I had this same problem but a different solution. To solve my problem, I had to convert text to columns for each column that contains data I want the formulas to count. I do this frequently, so I recorded a macro to quickly convert all relevant columns each time I refresh the workbook. Scenario: I have a workbook with formulas on one sheet. The formulas use data on another sheet that I refresh by pasting data in from another Excel report I download from a proprietary web-based software. Problem: formulas in worksheet work with data I enter, but not data I paste in. Solution: paste new data in a separate worksheet in the workbook. Convert text to columns for each column that has data the formulas reference.

Improve this answer
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Oct 21, 2022 at 20:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .