 Search:

# Data Sum rows not hidded

Asked By: Jaxson    Date: Feb 03    Category: MS Office    Views: 1671

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.

Share:

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

It works GREAT.
=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

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