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

Location

Critical Level

Jan

Feb

Mar

Apr

north

high

south

low

east

med

west

Critical

<tbody>
</tbody>

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:

|*User ID:* z123456|*User Name:* Last First|*Telephone:* +19999999999|*Region:* RN|*Country:* US|*Location:* Gray Court|*StructureCode:* | | |[mailto:first.last@zf.com]|[]|

<tbody>
</tbody>

 

[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

Ticket	Priority <tbody> </tbody>	Created <tbody> </tbody>	Updated <tbody> </tbody>	Resolved <tbody> </tbody>	Status <tbody> </tbody>	Affected Enduser Details <tbody> </tbody>
1	low	9-4-2014	9-5-2014	9-6-2014	closed	| *User ID:* z123456| *User Name:* micky mouse | *Telephone:* +5555555555| *Region:* RN | *Country:* US | *Location:* Northville | *StructureCode:* | | | [mailto: ] | <tbody> </tbody>
2	medium	9-5-2014	9-6-2014		progress	| *User ID:* z234567| *User Name:* daffy duck | *Telephone:* +6666666666 | *Region:* RN | *Country:* US | *Location:* Gray Court | *StructureCode:* | | | [mailto:] | <tbody> </tbody>
3	critical	9-6-2014	9-7-2014	9-8-2014	Closed	| *User ID:* z345678| *User Name:* Winnie TePuhh | *Telephone:* +7777777777 | *Region:* RN | *Country:* US | *Location:* Lapeer | *StructureCode:* | | | [mailto: ] | <tbody> </tbody>

<tbody>
</tbody>

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

Location	Priority		Jan	Feb
Northville	Low
Grey Court	Medium

<tbody>
</tbody>

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.