I ran into the following problem in EXCEL 2010 and 2013:

- I have a very large file that is roughly 43,000 rows long
- I filtered the 43,000 rows to yield only specific types of items
- In a column to the right of the filtered data I pasted a relative formula in each row that added 3 at each row


- The quantities of items in each row was 3, so I thought the paste would yield a sum of all filtered items
- The formula paste actually took place in all 43,000 rows instead of just the visible filtered rows yielding an erroneous total (much larger)
- At least I think it is erroneous.

Should Excel paste the formula even in rows that have been filtered out and are no longer visible? If so, does that make sense? I suspect I am missing something, and would appreciate any inputs.

Thanks,

Perhaps use the SUBTOTAL function with finction_number 109 ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Peco, did you mean to post that reply to a different question? I don't understand what it means in this context.

(In fact, I don't know what "finction" is.)

Rabend, I don't use the filter much but as I understand it it's intended to be a way of viewing details; as you're saying yourself, it doesn't have an effect on the SUM function, nor on any other function so far as I know.

I take it you want a sum of only those 3s that appear in certain cells, and you can do that but you'll need either SUMIF or COUNTIF. Do you know already how to do that, or shall I explain?

And by the way, as I said I don't use filters much so if someone else here knows that I'm mistaken about them not affecting functions, please chime in.

"finction" was a typo for "function" As far as I understood OP is trying to sum filtered data which can be done with SUBTOTAL

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post