Excel电子表格中可见(或过滤)范围的总和 [英] Sum visible (or filtered) range in an Excel Spreadsheet

问题描述

在Excel中，我需要过滤并显示 COUNTIF & 全局范围和可见(或过滤)范围的 SUM .

In Excel, I need to filter and display the COUNTIF & SUM of both the global range and the visible (or filtered) range.

我已经可以显示 COUNTIF & 全局范围的 SUM ，并带有以下代码.

I can already display the COUNTIF & SUM of the global range with the following code.

AtmCount = Application.WorksheetFunction.CountIf(Range("X3:X4533"), ">0")
AtmSum = Application.WorksheetFunction.Sum(Range("X3:X4533"))

我还可以获取可见(或过滤的)范围的 COUNT ，如下所示:

I can also get the COUNT of the visible (or filtered) range as follows:

AtmCurrentCount = Range("X3:X4533").SpecialCells(xlCellTypeVisible).Count

但是，这仍然使可见(或过滤后)范围的 SUM 突出.

However, this still leaves the SUM of visible (or filtered) range outstanding.

AtmCurrentSum = ???

我真的卡住了.拜托，有人可以帮我吗?

I really stuck. Please, can somebody help me?

推荐答案

这将完成您想要的.将visibleTotal设置为总计的适当数据类型，然后更改ws和rng对象以匹配工作簿中的内容.

This will do what you want. Set visibleTotal to the appropriate data type for the total, and change the ws and rng objects to match what you have in your workbook.

Sub SumVisible()
    Dim ws As Worksheet
    Dim rng As Range
    Dim visibleTotal As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("B1:B7")

    ws.AutoFilterMode = False
    rng.AutoFilter field:=1, Criteria1:=5

    visibleTotal = Application.WorksheetFunction.Sum(rng.SpecialCells(xlCellTypeVisible))
    ' print to the immediate window
    Debug.Print visibleTotal
End Sub

如果您只想对过滤范围的一部分求和(例如，对A列进行过滤，但想要B列的总和)，请参见以下问题和答案:

In case you only want to sum part of the filtered range (e.g. you filter on column A but want the sum of column B), see this question and answer: Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table.

这篇关于Excel电子表格中可见(或过滤)范围的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！