Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I want to sum data in a column. Simple =SUM(AB1:B500)

Now I have a macro that hides rows based on user imput in another

column. If they were filtered, I'd use =Subtotal(9,B1:B500) but it's

a macro that simply hides the row and Subtotal sums even the hidden

row.

Is there another equation I can use to count column B if

Column A = blank, or any letter, but NOT any number between 1 and

10000.

I thought about making another column (C) with

=IF(AND(A4>1,A4<10000),B4,"") then summing (C) and subtracting this

number from =SUM(B4:B500) but is there another eligant way?

I have also thought of =Sumif() but that doesn't work if the

criteria is another column, just a value.

=SUM(B1:B500)-SUMPRODUCT(--(A1:A500>1),--(A1:A500<10000),B1:B500)

It works GREAT.

just had to change to:

=SUM(B1:B500) -SUMPRODUCT( --(A1:A500> 0),--(A1: A500<10000) ,B1:B500)

from:

=SUM(B1:B500) -SUMPRODUCT( --(A1:A500> 1),--(A1: A500<10000) ,B1:B500)

to make "1" a non-read criteria.

Just wished I'd waited until I got your eMail. I just spent the last few hours

manually editing all the formulas in B to read as zero if 1-10000 was in A. FUN

not

=IF(AND(A18>0,A18<10000),0,equation already in cell) and they were all

different equations and on two different sheets.

Thanks for the help. I like your way better. Might even change them all back

(I have a back up file and can just copy and paste the old data back in) and use

your solution.

Didn't find what you were looking for?
Find more on Data Sum rows not hidded
Or get search suggestion and latest updates.

Related Topics:

- SUM content of rows
- Summing on varying number of rows in Excel using VBA
- Move data from a column of row couplets to multiple rows of colum
- Counting the rows If the next row value is same as the previous row
- Delete cell data but NOT entire row/column
- Delete Part of Row and move next Row Down Up
- deleting rows with certain data in a column
- Insert Rows, based on data in range, using VBA
- Moving data in the last cell of row to another column cell
- retrieving last used Column with data in particular in row
- Excel VBA to delete a row of data
- Write a shell program to find the sum of the series sum=1 + 3 + 5 + 7 + …+ n
- Code for Write a shell program to find the sum of the series sum=1 + 5 + 9 + ...+ n in Unix
- Sum colunm total in repeater
- Calculate Total Sum
- sum of amount deliverd group
- count the sum of a column
- custom Sum function?
- Summing a range of variable size
- Loop, sum values and paste
- VBA code to sum
- Using Macro to automatically sum a variable range of rows?
- sum in columns
- Summing a range
- getting the sum of the last 20 cells