Excel 2003 使用多个条件对表格数据求和

发布于 2024-12-27 18:03:17 字数 492 浏览 1 评论 0原文

我已经在这个问题上苦苦挣扎了一段时间了...我在一张名为 PickupData

有一张表格,我试图对收入(col d)进行求和,其中 org_code(col a) = 第 18 行中的值和月份(col c) = a 列中的值

在此处输入图像描述

我尝试过使用 SUMPRODUCT, SUM(IF(blah,IF(blah,x,0),0) (按 ctrl-shift-enter),甚至条件总和向导我认为我不能使用 DSUM,因为它需要为条件参数传递单元格范围,

因为我认为这是这样。可能会影响

谁能提供一个可行的解决方案?

这是测试工作簿:这里

谢谢

I've been struggling with this one for a while... I have the following data table on a sheet called PickupData

enter image description here

I then have a sheet where i'm trying to sum income (col d) where org_code (col a) = the value in row 18 and month (col c) = the value in column a

enter image description here

I've tried with SUMPRODUCT, SUM(IF(blah,IF(blah,x,0),0) (pressing ctrl-shift-enter) and even the conditional sum wizard. I don't think i can use DSUM as it requires a cell range to be passed for the criteria parameter.

I also tried un-merging the cells as i thought this could be effecting the results.

Can anyone provide a working solution?

Heres the test workbook: here

Thanks

Lee

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

你的背包 2025-01-03 18:03:17

Sumproduct 适合您:

=SUMPRODUCT(D1:D8,--(A1:A8=OrgRef),--(C1:C8=MonthRef))

OrgRef 和 MonthRef 是过滤器地址的占位符。

在您的示例书中,组织代码是文本,而您输入 26 的“Org_Code”字段是数字。他们不会匹配。将您的组织代码转换为数字,或者在 26 前面加上 ' 将其标记为字符串。

Sumproduct will work for you:

=SUMPRODUCT(D1:D8,--(A1:A8=OrgRef),--(C1:C8=MonthRef))

OrgRef and MonthRef are placeholders for the address of your filters.

In your example book the Org code is text while your "Org_Code" field where you have 26 entered is a number. They won't match. Either convert your org codes to numbers or preface 26 with a ' to mark it as a string.

生活了然无味 2025-01-03 18:03:17

您是否尝试过:

SUMIF()
SUMIFS()

或者 Excel 宏?

代码

Sub Test1()
    Dim rRange As Range
    Set rRange = Range("C:C")

    Dim iSum As Integer
    For Each rRange In rRange

        If (rRange.Offset(0, -2) = 18) Then
            iSum = iSum + rRange.Value
        End If

        If (IsEmpty(rRange.Offset(1, 0))) Then
            Exit For
        End If
    Next rRange

    Debug.Print iSum
End Sub

Have you tried:

SUMIF()
SUMIFS()

or, Excel Macros?

Code

Sub Test1()
    Dim rRange As Range
    Set rRange = Range("C:C")

    Dim iSum As Integer
    For Each rRange In rRange

        If (rRange.Offset(0, -2) = 18) Then
            iSum = iSum + rRange.Value
        End If

        If (IsEmpty(rRange.Offset(1, 0))) Then
            Exit For
        End If
    Next rRange

    Debug.Print iSum
End Sub
静谧幽蓝 2025-01-03 18:03:17

SUM(IF(... 应该可以工作,但是是的,您需要取消合并单元格,因为数组公式在合并单元格中不起作用。在 B19 中输入以下内容应该可以工作(记住 ctrl+shift+enter)

=SUM(IF(PickupData!$A$2:$A$8=B$18,IF(PickupData!$C$2:$C$8=$A19,PickupData!$D$2:$D$8,0),0))

The SUM(IF(... should work, but yes you will need to un-merge your cells because array-formulas do not work in merged cells. The following entered into B19 should work (remembering to ctrl+shift+enter)

=SUM(IF(PickupData!$A$2:$A$8=B$18,IF(PickupData!$C$2:$C$8=$A19,PickupData!$D$2:$D$8,0),0))
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文