Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Data Sum rows not hidded

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

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: 

 

2 Answers Found

 
Answer #1    Answered By: Billy Evans     Answered On: Feb 03

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

 
Answer #2    Answered By: Isam Bashara     Answered On: Feb 03

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.




Tagged:        

 
 
 
 

Related Post