+ Reply to Thread
Results 1 to 4 of 4

SumIf alternative to calc filtered rows only

  1. #1
    Registered User
    Join Date
    11-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    SumIf alternative to calc filtered rows only

    Hi,

    I have searched for solutions to this, but can't get my head around them...


    I have this code which is returning the value I'm expecting on all my data

    CountV1 = Application.WorksheetFunction.SumIf(Columns(DefinedColumn), 1, Columns("C:C"))

    This gives me the sum of the data shown in Column C, for items when there is a 1 in the column 'DefinedColumn'

    Equally, this is returning the same value:

    CountV2 = Application.WorksheetFunction.SumProduct((Columns(DefinedCol)), Columns(("C:C")))


    However, I want to run filters on the data and recalculate these values based only on those who fit the criteria of that filter, but my code continues to calculate it all on the total dataset. I think subtotal might be my answer, but can't work out how to adapt my code to fit. Would anyone be so kind as to help!

    Cheers!!

    Matt

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: SumIf alternative to calc filtered rows only

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: SumIf alternative to calc filtered rows only

    Hi Tim,

    Thanks for trying to help, I really appreciate it. I am still having some trouble though :-(

    I've added some more of my code (to show how I'm defining my column) in case this is part of the problem. I've got script before this which gets me to a cell I want to use as my column reference. This uses the text in the column header as a reference for a search.

    I've added, but adapted, some of the code you gave. Without it, I had some mismatch errors.

    Dim SkyCol As Long
    SkyCol = ActiveCell.Column
    Set SkyRange = Columns(SkyCol).SpecialCells(xlVisible)
    Countv1 = Application.WorksheetFunction.SumIf(SkyRange, 1, Columns(3).SpecialCells(xlVisible))


    The code here now works well when no filters are applied to my data, but I get a Run-time error '1004' saying "Method 'Sumif' of object 'WorksheetFunction' failed on the Countv1 row of code when a filter is applied

    This is driving me mad!

    Matt

  4. #4
    Registered User
    Join Date
    11-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: SumIf alternative to calc filtered rows only

    Hi,

    I've worked a solution I'm happy with now, so posting again in case anyone else find this thread useful at a later stage

    I found that the formula I needed was as follows:

    =SUMPRODUCT(G:G, SUBTOTAL(109, OFFSET(C:C,ROW(C:C) - MIN(ROW(C:C)), 0, 1)))

    so could put this into VBA as

    FixedFormulaSum = Application.Evaluate("SUMPRODUCT(G:G, SUBTOTAL(109, OFFSET(C:C,ROW(C:C) - MIN(ROW(C:C)), 0, 1)))"

    However, I wanted to vary column G in this example.

    Dim ProdColNum As Long
    ProdColNum = ActiveCell.Column 'This was my way of identifying the column I wanted to vary (I landed the script on to, for example, cell G1 before running this.
    Dim ProdColLetter As String
    ProdColLetter = Split(Cells(1, ProdColNum).Address, "$")(1)
    VariableFormulaSum = Application.Evaluate("SUMPRODUCT(" & ProdColLetter & ":" & ProdColLetter & ", SUBTOTAL(109, OFFSET(C:C,ROW(C:C) - MIN(ROW(C:C)), 0, 1)))")

    It took me a while (longer than it should have done!) to work out what I could piece together the formula within the Evaluate function using & as connectors. I'd previously tried to put it all together as a separate string variable and enter that into the Evaluate, but that did not want to run for me.

    Cheers

    Matt

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Alternative to If Function on Open Office Calc
    By lucaslu118 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-24-2016, 03:04 PM
  2. Paste Back to Filtered Sheet or Alternative?
    By coherent in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2013, 08:35 AM
  3. Need an Alternative to SumIF
    By caliskier in forum Excel General
    Replies: 4
    Last Post: 02-22-2012, 01:32 PM
  4. Alternative to SUMIF
    By ltmaiyk in forum Excel General
    Replies: 2
    Last Post: 01-22-2010, 12:06 AM
  5. SUMIF Alternative
    By rwall in forum Excel General
    Replies: 1
    Last Post: 04-05-2009, 07:21 PM
  6. SUMIF Alternative?
    By qflyer in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 09-06-2005, 10:05 AM
  7. SUMIF Alternative?
    By qflyer in forum Excel General
    Replies: 1
    Last Post: 06-20-2005, 02:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1