Excel SUMIFS检查列是否包含文本 [英] Excel SUMIFS checking if a column contains text

问题描述

我有一个excel文档，数据透视表中有数据，该数据显示有几个子类别.我需要检查column A中任何地方的文本是否包含特定单词，然后还要检查column C中的文本以查看其是否包含特定的确切短语.

I have an excel document with data in a PivotTable that is displayed with several subcategories. I need to check if text anywhere in column A contains a particular word and then also check the text in column C to see if it contains a particular exact phrase.

到目前为止，我想出了

=SUMIFS('tab1'!D5:D300, 'tab1'!A5:A300, "WORD", 'tab1'!C5:C300, "PHRASE")

我遇到的问题是此公式正在检查的excel选项卡显示这样的信息，除了第一个短语(我假设这是因为第一个短语共享)以外，它将为我提供"0"的结果类别为"WORD")的行:

The issue I've run into is that the excel tab this formula is checking displays information like this and it will give me the result of "0" for anything but the first phrase (which I assume is because the first phrase shares a row with the category "WORD"):

Excel信息示例:

Excel Information Example:

PivotTable中的数据全年可能发生巨大变化，这是它显示信息的方式，因此我无法控制这些信息的显示方式.

The data in the PivotTable can change drastically throughout the year, and it is how it displays the information so I have no control over how that information is presented.

是否有一种方法可以随时检查column A是否包含特定单词，但不一定需要在相应的行中进行匹配.

Is there a way to check if column A at any point contains a particular word, but doesn't necessarily need to match up in the appropriate row.

推荐答案

=IF(INDEX(C5:C300,MATCH("WORD 2",A5:A300,0),0)<>"PHRASE","",INDEX(D5:D300,MATCH("WORD 2",A5:A300,0)))

希望它会有所帮助..(:

Hope it'll help.. (:

这篇关于Excel SUMIFS检查列是否包含文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！