2

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.

So far, I'd come up with

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

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 Information Example:

enter image description here

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.

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.

Bharata
  • 13,509
  • 6
  • 36
  • 50
Daniel Rivera
  • 21
  • 1
  • You don't need a sum or anything - just to confirm that both A and C have "WORD" and "PHRASE" respectively, right? What about using `AND`, `ISERROR`, and `MATCH`? – BigBen Jul 20 '18 at 20:12
  • I technically don't need the sum, no. Just the number displayed next to each specific PHRASE. I'll try the things you mentioned though cause that is a good point. – Daniel Rivera Jul 20 '18 at 20:22
  • 1
    you know you can repeat i.e. fill down the column with word in? It is a setting for the pivottable. – QHarr Jul 20 '18 at 23:01
  • Qharr, your suggestion worked great for the formula I was using. I didn't know that was a pivottable setting and it makes things a lot easier for me! – Daniel Rivera Jul 23 '18 at 16:58

1 Answers1

0
=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.. (:

p._phidot_
  • 1,913
  • 1
  • 9
  • 17