Counting the number of a instance of a word in a string if another cell equals a specific cell

[aldea187]

Hello everyone,

Looking for some help again on the our love hate with with this great application.

I have a base statement of = =IF(Jan!C[1]=RC[-2], SUMPRODUCT(LEN(Jan!C[11])-LEN(SUBSTITUTE(Jan!C[11],RC[-3],"")))/LEN(RC[-3]), " ")

I am looking to check if a column equals a cell, and if so count a cell in that row for another keyword

[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Critical Level[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]north[/TD]
[TD]high[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]south[/TD]
[TD]low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]east[/TD]
[TD]med[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]west[/TD]
[TD]Critical[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The data is by month and has about 4000 records per. Let me know if any other data is needed.

Thanks in advance

 

[aldea187]

In addition, the reason the Len statement and sumproduct is because there a list of items in the cell that it needs to scan through to get the data. An example would be:

[TABLE="width: 1790"]
<tbody>[TR]
[TD="class: xl162, width: 1790"]|*User ID:* z123456|*User Name:* Last First|*Telephone:* +19999999999|*Region:* RN|*Country:* US|*Location:* Gray Court|*StructureCode:* |
| |[mailto:first.last@zf.com]|[]|[/TD]
[/TR]
</tbody>[/TABLE]

 

[aldea187]

And looking at it now, my origional thought for statement is completely wrong.

 

[aldea187]

So to expand.

In one workbook, I have my data

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ticket[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl162, width: 74"]Priority[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl162, width: 108"]Created[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="class: xl162, width: 101"]Updated[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="class: xl162, width: 101"]Resolved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl162, width: 64"]Status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 1790"]
<tbody>[TR]
[TD="class: xl162, width: 1790"]Affected Enduser Details[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]low
[/TD]
[TD]9-4-2014[/TD]
[TD]9-5-2014[/TD]
[TD]9-6-2014[/TD]
[TD]closed[/TD]
[TD][TABLE="width: 1790"]
<tbody>[TR]
[TD="class: xl162, width: 1790"] | *User ID:* z123456| *User Name:* micky mouse | *Telephone:* +5555555555| *Region:* RN | *Country:* US | *Location:* Northville | *StructureCode:* |
| | [mailto: ] | [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]medium[/TD]
[TD]9-5-2014[/TD]
[TD]9-6-2014[/TD]
[TD][/TD]
[TD]progress[/TD]
[TD][TABLE="width: 1790"]
<tbody>[TR]
[TD="class: xl162, width: 1790"] | *User ID:* z234567| *User Name:* daffy duck | *Telephone:* +6666666666 | *Region:* RN | *Country:* US | *Location:* Gray Court | *StructureCode:* |
| | [mailto:] | [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]critical[/TD]
[TD]9-6-2014[/TD]
[TD]9-7-2014[/TD]
[TD]9-8-2014[/TD]
[TD]Closed[/TD]
[TD][TABLE="width: 1790"]
<tbody>[TR]
[TD="class: xl162, width: 1790"] | *User ID:* z345678| *User Name:* Winnie TePuhh | *Telephone:* +7777777777 | *Region:* RN | *Country:* US | *Location:* Lapeer | *StructureCode:* |
| | [mailto: ] | [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


This data i have for each month. so in the other workbook, i have my data

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Priority[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]Northville
[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grey Court[/TD]
[TD]Medium[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What i am looking for is to count when northville has low priority and so on. Above is a mix of a few that i have used for other types of data pulls, but I am having issues at this point with this double check.

Hope this additional information helps.

 

[aldea187]

Bump.