Excel:如果vlookup返回特定文本，则计数实例数(多个) [英] Excel: If vlookup returns specific text, countifs number of instances (multiple)

问题描述

第一篇文章，但我被困住了，希望能有所帮助.我正在尝试使用嵌套的IF公式来确定VLOOKUP返回的值是否与同一工作簿中另一张纸上的特定文本匹配.如果是这样，则可以使用COUNTIFS函数来确定特定单元格中包含的特定文本出现在另一张工作表(也在同一工作簿中)的实例总数.

First post, but I am stuck and was hoping for some help. What I am trying to do is use a nested IF formula to determine whether the value returned by a VLOOKUP matches specific text on another sheet in the same workbook. If it does, to then use the COUNTIFS function to determine the sum of instances that specific text contained in a specific cell appears on another sheet (also in the same workbook).

这些公式中的一部分似乎有效，但是当我将其与其他公式结合使用时，它就会出错，并且我不知道为什么.

One part of these formulas seems to work, but when I combine it with others, it errors and I can't figure out why.

这可能很简单，但我将不胜感激.这是似乎起作用"的部分

It's probably something simple, but I'd appreciate any help. Here's the part that seems to "work"

=IF(VLOOKUP($A3,'Sheet1'!$B:$D,3,FALSE)="All",

(COUNTIFS('Sheet2'!$U:$U,'Sheet3'!$A3,'Sheet2'!$C:$C,"MM/DD/YYYY")
+
(COUNTIFS('Sheet2'!$R:$R,'Sheet3'!$A3,'Sheet2'!$C:$C,"MM/DD/YYYY")))

如果再加上一个括号，它仍然可以使用，但是似乎省略了一些全部" Vlookup结果...因此，为什么在上面的引号中加上了括号.虽然这可能是我的电子表格存在的问题，但我认为应该包括在内，以防万一我只是白痴.无论如何，当我输入另外两个IF语句时，都会出错.

If I put one more parenthesis, it works as is, but seems to omit some of the "All" Vlookup results... thus why I put works in quotations above. That might be an issue with my spreadsheet though, but felt I should include in case I'm just being idiotic. Regardless, when I input two additional IF statements, it errors.

我制作的最终公式如下所示(双行仅用于便于阅读):

The final formula I've crafted looks like this (double spaced for ease of reading only):

=IF(VLOOKUP($A3,'Sheet1'!$B:$D,3,FALSE)="All",

(COUNTIFS('Sheet2'!$U:$U,'Sheet3'!$A3,'Sheet2'!$C:$C,"MM/DD/YYYY")
+
(COUNTIFS('Sheet2'!$R:$R,'Sheet3'!$A3,'Sheet2'!$C:$C,"MM/DD/YYYY")))),

IF((VLOOKUP($A3,'Sheet1'!$B:$D,3,FALSE)="One",

(COUNTIFS('Sheet2'!$U:$U,'Sheet3'!$A3,'Sheet2'!$C:$C,"MM/DD/YYYY")),

IF((VLOOKUP($A3,'Sheet1'!$B:$D,3,FALSE)="Two",

(COUNTIFS('Sheet2'!$R:$R,'Sheet3'!$A3,'Sheet2'!$C:$C,"MM/DD/YYYY"),

IF((VLOOKUP($A3,'Sheet1'!$B:$D,3,FALSE)="Three",

(COUNTIFS('Sheet2'!$U:$U,'Sheet3'!$A3,'Sheet2'!$C:$C,"MM/DD/YYYY")),"0"))

有什么想法吗?

谢谢！

Ryan Olson

Ryan Olson

推荐答案

只需清理一些包围即可.让我知道怎么回事，因为我没有改变公式.

Just cleaned up some bracketing. Let me know how it goes as I didn't alter the formula otherwise.

=IF(VLOOKUP($A3,Sheet1!$B:$D,3,FALSE)="All",
COUNTIFS(Sheet2!$U:$U,Sheet3!$A3,Sheet2!$C:$C,"MM/DD/YYYY")+(COUNTIFS(Sheet2!$R:$R,Sheet3!$A3,Sheet2!$C:$C,"MM/DD/YYYY")),
IF(VLOOKUP($A3,Sheet1!$B:$D,3,FALSE)="One",
COUNTIFS(Sheet2!$U:$U,Sheet3!$A3,Sheet2!$C:$C,"MM/DD/YYYY"),
IF(VLOOKUP($A3,Sheet1!$B:$D,3,FALSE)="Two",
COUNTIFS(Sheet2!$R:$R,Sheet3!$A3,Sheet2!$C:$C,"MM/DD/YYYY"),
IF(VLOOKUP($A3,Sheet1!$B:$D,3,FALSE)="Three",
COUNTIFS(Sheet2!$U:$U,Sheet3!$A3,Sheet2!$C:$C,"MM/DD/YYYY"),"0"))))

这篇关于Excel:如果vlookup返回特定文本，则计数实例数(多个)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！