#### justinkim12

##### New Member

- Joined
- Jun 10, 2008

- Messages
- 4

I have the following formula in my spreadsheet, and it seems to be slowing the spreadsheet considerably:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I$94:I$551,ROW(I$94:I$551)

-MIN(ROW(I$94:I$551)),,1))*(I$94:I$551=$A30))

Here is what I am trying to do:

I have several columns of numbers. I want to count the number of times a particular numerical value shows up in each column. I want to be able to filter the data and have this formula display the number of times a particular numerical value shows up for the filtered values only.

I copy and paste the above formula across rows and columns to cover all columns I want. In the above formula, I am referencing data in column I, and comparing it to the value in cell A30.

The formula works, but it really slows down the spreadsheet. In my current spreadsheet, I have 20 rows and about 140 columns with this formula which references about 460 rows of data across those 140 columns.

Is there a way write the formula more efficiently or perhaps break apart the formula into smaller pieces? I don't mind copying and pasting sets of formulas.

Thanks for your help. I'm really stumped on this!

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I$94:I$551,ROW(I$94:I$551)

-MIN(ROW(I$94:I$551)),,1))*(I$94:I$551=$A30))

Here is what I am trying to do:

I have several columns of numbers. I want to count the number of times a particular numerical value shows up in each column. I want to be able to filter the data and have this formula display the number of times a particular numerical value shows up for the filtered values only.

I copy and paste the above formula across rows and columns to cover all columns I want. In the above formula, I am referencing data in column I, and comparing it to the value in cell A30.

The formula works, but it really slows down the spreadsheet. In my current spreadsheet, I have 20 rows and about 140 columns with this formula which references about 460 rows of data across those 140 columns.

Is there a way write the formula more efficiently or perhaps break apart the formula into smaller pieces? I don't mind copying and pasting sets of formulas.

Thanks for your help. I'm really stumped on this!

Last edited: