Index Match Formula with multiple criteria

[Trikson]

Hi,

I'm trying to identify items that have two specific values (<blank> in Owner Name) spread across two rows (Owner Role="Primary Site Owner" OR "Secondary Site Owner") with one common identifier (Site URL) and, if the conditions are met, insert a note in the column of these row(s) (Owner status). I believe index match is my best shot, but am open to all other suggestions.

What the data looks like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Site URL[/TD]
[TD]Owner Role[/TD]
[TD]Owner Name[/TD]
[TD]Owner Status[/TD]
[/TR]
[TR]
[TD]URL1[/TD]
[TD]Primary Site Owner[/TD]
[TD]Ted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URL1[/TD]
[TD]Secondary Site Owner[/TD]
[TD]Susan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URL2[/TD]
[TD]Primary Site Owner[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URL3[/TD]
[TD]Primary Site Owner[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URL3[/TD]
[TD]Secondary Site Owner[/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URL2[/TD]
[TD]Secondary Site Owner[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URL4[/TD]
[TD]Primary Site Owner[/TD]
[TD]Ted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URL4[/TD]
[TD]Secondary Site Owner[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URL4[/TD]
[TD]Functional Site Owner[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I Need to achieve:

End state is I have a Pivot with count of all sites that have no Primary and Secondary Site Owners assigned (Owner Name for these Owner Roles are blank). The issue I encounter when trying to do that on the row data is that Pivot counts the cases when even one of the conditions is met (either Primary or Secondary Owner is blank) which skews the data. As you can see in the example, the URLs are all over the place, and also there are different owner roles in the raw data that I am not interested in for this Pivot (but am hesitant to delete as I need it for different pivot). That means however that I cannot just have the formula check the row below, as this will create bogus results.

Therefore I believe what I need to do is to come up with a formula that would check the Owner Name Value for the Owner Role of Primary Site Owner, and if it is blank, formula needs to search for row where the same site URL is listed for Owner Role of Secondary Site Owner and check for the value of Owner Name for that row. If it is also blank, it should insert "Owners Missing" text in the Owner Status column of both rows (the one with missing Primary Site Owner and the one with missing Secondary Site Owner) and move to the next site.

If the value of Secondary Site Owner is anything different than blank, the formula does not need to insert anything and can simply move to next site. Same if the Primary Site Owner value is not blank, formula does not need to check for the Secondary Site Owner value and can move to next site.

Appreciate any suggestions on how to tackle this one.

Thanks in advance,
Trikson

</blank>

 

[Aryatect]

Hi,

Hopefully this will help:

Book1
	A	B	C	D
1	Site URL	Owner Role	Owner Name	Owner Status
2	URL1	Primary Site Owner	Ted
3	URL1	Secondary Site Owner	Susan
4	URL2	Primary Site Owner		Owner Missing
5	URL3	Primary Site Owner
6	URL3	Secondary Site Owner	John
7	URL2	Secondary Site Owner		Owner Missing
8	URL4	Primary Site Owner	Ted
9	URL4	Secondary Site Owner
10	URL4	Functional Site Owner
Sheet4

Cell Formulas
Range	Formula
D2	=IF(SUMPRODUCT(($A$2:$A$10=A2)*(($B$2:$B$10="Primary Site Owner")+($B$2:$B$10="Secondary Site Owner"))*($C$2:$C$10<>""))>0,"","Owner Missing")

 

[Trikson]

1. Works like a charm
2. I have yet another formula to learn

Seriously though -thank you very much Aryatect!

 

[Aryatect]

Glad could help, thanks for the feedback.