什么公式可以使我根据不同单元格的数据完成某些SUM? [英] What formula will allow me to complete certain SUMs depending on the data of a different cell?

问题描述

这是一个示例，其中显示了一年内交易的获利和亏损.

This is an example spreadsheet of trading wins and losses within a year.

我正在尝试在工作表 MONTHLY STATS-SPREADS 上获取单元格 I6 ，以在没有 L的情况下填充 0 用于在表 SPREADS LOG 上的 3月21日和 3月21日日期之间进行计数.

I am trying to get cell I6 on sheet MONTHLY STATS - SPREADS to populate a 0 if there have been no L's to count between the dates of 1 Mar 21 and 31 Mar 21 on sheet SPREADS LOG.

但是，如果单元格 H6 中存在数据，我只希望填充此 0 .如果 H6 中没有数据，我希望 I6 为空白.(请注意，由于稍后我试图对完整的 I 列取平均值，因此我无法使用数字格式将这些 0 隐藏在单元格 I6 中在工作表中)

But I only want this 0 to populate if there is data present in cell H6. If there is no data in H6 I would like I6 to be blank. (note I cannot use a number format to hide these 0's in cell I6 as I am trying to average the complete I columnm later on in the sheet)

在执行此操作时，单元格 I6 还必须能够运行 COUNTIFS & SUM 公式如下所示.

Whilst it is doing this cell I6 must also be able to run the COUNTIFS & SUM formulas shown below.

重要说明:这也必须以相反的方式进行.例如如果在日期之间没有 W ，我希望将数据放入 LOST后在 WON 列中填充 0 列.

IMPORTANT NOTE: This must also work the reverse way. e.g. if there are no W's between the dates I want a 0 populated in the WON column once data is put into the LOST column.

这是我当前在单元格 I6 中使用的公式:

This is the formula I am currently using in cell I6 :

= IF(H6 ="，，"，IF(COUNTIFS('SPREADS LOG'！P:P，"L"，'SPREADS LOG'！R:R，> =''& DATE(2021,3,1)，'SPREADS LOG'！R:R，''< ="=& DATE(2021,3,31))，COUNTIFS('SPREADS LOG'！P:P，"L"，"SPREADS LOG"！R:R，"＝＝"& DATE(2021,3,1)，"SPREADS LOG"！R:R，<=& DATE(2021,3,31))，"0"))

如果任何人都可以对此进行调整或建议进行修改，那么它会很棒.

If anyone could tweak this or suggest an edit that would be great.

推荐答案

这是一个可以满足您期望的公式.

Here is a formula that will do what you're intending.

单元格 H6 :

=LET(LOSSES,COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<="&EDATE($A6,1)),WINS,COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1)),IF(AND(LOSSES=0,WINS=0),"",WINS))

单元格 I6 :

=LET(LOSSES,COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<="&EDATE($A6,1)),WINS,COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1)),IF(AND(LOSSES=0,WINS=0),"",LOSSES))

使用空字符串"" 代替 0 可以完美地从 SUM 和 COUNTIFS 中排除单元格code>公式而不破坏它们.

Using an empty string "" instead of 0 works perfectly for excluding the cell from SUM and COUNTIFS formulas without breaking them.

请注意，您不能使用其他单元格的值来决定是否填充 0 值，因为这会创建循环引用.

Please note that you cannot use the other cell's value for deciding whether to populate a 0 value or not, since this would create a circular reference.

没有 LET 的版本:

单元格 H6 :

=IF(AND(COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1))=0,COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1))=0),"",COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1)))

单元格 I6 :

=IF(AND(COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1))=0,COUNTIFS('SPREADS LOG'!$P:$P,"W",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1))=0),"",COUNTIFS('SPREADS LOG'!$P:$P,"L",'SPREADS LOG'!$R:$R,">="&$A6,'SPREADS LOG'!$R:$R,"<"&EDATE($A6,1)))

这些公式假定列 A 中的日期包含日期值(例如，三月份的 01.03.2021 )，并使用自定义格式设置格式.这使您可以简单地复制整个列的公式，而无需手动更改日期.我强烈推荐这种方法，因为它与当前的方法一样没有缺点，但是当您将其添加到自动化功能库中时，它可能具有巨大的发展潜力.如果您决定坚持使用自己的方法，则需要更改 COUNTIFS 中的条件.

These formulas assume the dates in your column A contain date values (e.g. 01.03.2021 for March) and are formatted with a custom formatting. This allows you to simply copy the formula for the entire column without manually having to change the dates. I would strongly recommend this approach as it has no disadvantages to your current approach, but a potentially tremendous upside when you add it to your repertoire of automation. If you decide to stick to your approach, you will need to change the conditions inside the COUNTIFS.

编辑是因为我误解了问题并提供了错误的答案.

这篇关于什么公式可以使我根据不同单元格的数据完成某些SUM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！