SUMIFS 使用动态标准返回 0，标准范围和总和范围在另一张纸上 [英] SUMIFS returns 0 using dynamic criteria, with criteria range and sum range on another sheet

问题描述

任何人，

我与 excel 客户服务进行了交谈，但没有成功.我使用了公式生成器(请参阅附加的屏幕截图)来确保公式的每个元素都是正确的，并返回我尝试引用的标准的值.

I've chatted with and called excel customer service with no luck. I used the formula builder (please see attached screenshot) to make sure each element of the formula is correct and returns the value for the criteria I'm trying to reference.

一切都是准确的，但它返回的值为 0.当我在实际工作表中做同样的事情时，数据存储在(并单击标准范围内的标准单元格)它返回准确的值?！我不确定为什么它在另一张纸上不起作用.我用来选择的值是动态的，并且会随着下拉菜单的变化而变化.我有另一个高级工作簿(我没有创建)，它做同样的事情并完成一个更复杂的公式，但实际上有效，所以我不确定为什么这会返回 0 值.

Everything is accurate, but it returns a value of 0. When I do the same thing in the actual sheet the data is stored in (and click a criteria cell within the criteria range) it returns the accurate value?! I'm not sure why it won't work on the other sheet. The values I am using to select are dynamic and change with a drop down. I have another, advanced, workbook (I did not create) that does the same thing and completes an even more complicated formula, but actually works so I'm not sure why this is returning a 0 value.

照片和代码/语法:动态选择、示例 2 工作，工作示例 1，Formula Builder、CountIFs、高级电子表格工作，VLOOKUP

Photos and code/syntax: Dynamic Selection, Example 2 of it working, Example 1 of it working, Formula Builder, CountIFs, Advanced Spreadsheet working, VLOOKUP

=SUMIFS('GFEBS Pull'!Q:Q,'GFEBS Pull'!G:G,FMCOP!$C$20,'GFEBS Pull'!H:H,FMCOP!B23)

或:

=SUMIFS('GFEBS Pull'!Q:Q,'GFEBS Pull'!G:G,'FMCOP'!$C$20,'GFEBS Pull'!H:H,'FMCOP'!B23)

当我在 FMCOP 工作表名称周围键入 ' 时，它们会消失吗?我也试过在没有运气的情况下锁定GFEBS Pull"表上的列.单元格 B23 未锁定，因为我要将公式向下复制以引用其他单元格.任何帮助表示赞赏！

When I type ' around FMCOP sheet name, they disappear? I've also tried to lock the columns on the 'GFEBS Pull' sheet with no luck. Cell B23 is not locked because I'm going to copy the formula down to reference other cells. Any help is appreciated!

推荐答案

在这个截图 你可以清楚地看到 FMCOP!C20 和 FMCOP!B23 都有前置空格；例如HHC".

In this screenshot you can clearly see that both FMCOP!C20 ansd FMCOP!B23 have prefacing spaces; e.g. " HHC".

由于"HHC"永远不会匹配"HHC"，所以修正从'同一张截图中的下表'返回的数据.

Since " HHC" will never match "HHC", fix the data returned from 'the lower table in the same screenshot'.

文本到列，固定宽度，完成应该这样做.您可以调整原始公式，如

A Text-to-Columns, Fixed Width, Finish should do this. You could adjust the original formula like,

=SUMIFS('GFEBS Pull'!Q:Q, 'GFEBS Pull'!G:G, TRIM(FMCOP!$C$20), 'GFEBS Pull'!H:H, TRIM(FMCOP!B23))

我会警告不要使用后一种创可贴"修复.修复原始数据；不要立即使用创可贴.

I would caution against the latter 'bandaid' fix. Fix the original data; do not apply bandaids on-the-fly.

这篇关于SUMIFS 使用动态标准返回 0，标准范围和总和范围在另一张纸上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！