Does this layout look anything like your sheet?

| A | W | Z | AC | AD | AE | CC | CD | CE | CF | DU | DV | DW | DX |
---|

1 | 47 | | | | | | | | | | | | | |

2 | | | | | | | | | | | | | | |

3 | | | | | | | | | | | | | | |

4 | | | | | | | | | | | | | | |

5 | | | | | | | | | | | | | | |

6 | | | | | | | | | | | | | | |

7 | | | | | | | | | | | | | | dx7 |

8 | | | | | | | | | | | | | | |

9 | | | 1 | dv15 | | | | | | | | | | |

10 | | | 2 | | | | | | | | | | | |

11 | | dx7 | 3 | | | | | | | | | | | |

12 | | | 4 | | dv15 | | | | | | | | | |

13 | | | 5 | | | | | dv15 | | | | | | |

14 | | | 6 | | | | | | | | | | | |

15 | | | 7 | dv15 | dv15 | | | dv15 | | dv15 | | dw15 | | |

16 | | | 8 | | | | | | | | | | | |

17 | | | 9 | | | dv15 | | | | | | | | |

18 | | | 10 | | | | | | | | | | | |

19 | | | | | | | | | | | | | | |

20 | | | | | | | | | | | | | | |

<tbody>

</tbody>

**Sheet12**

**Array Formulas**
Cell | Formula |
---|
A1 | {=IF(OR(DW15="",DW15="N/A",COUNTIF(W9:W208,DX7)=0),"",SUM(MMULT(--(AC9:AW208=DV15),TRANSPOSE(COLUMN(AC9:AW208))^0)+MMULT(--(CD9:CX208=DV15),TRANSPOSE(COLUMN(CD9:CX208))^0))))} |
---|
<tbody>
</tbody> **Entered with Ctrl+Shift+Enter.** If entered correctly, Excel will surround with curly braces {}.
**Note: Do not try and enter the {} manually yourself** |

<tbody>

</tbody>

The formula is in A1, the other ranges are just filled in with data based on what I can infer from the formula. It returns 47, which is calculated as follows:

DV15 occurs 1 time in row 9, times z9 = 1

DV15 occurs 1 time in row 12, times z12 = 4

DV15 occurs 1 time in row 13, times z13 = 5

DV15 occurs 4 times in row 15, times z15 = 4 * 7 = 28

DV15 occurs 1 time in row 17, times z17 = 9

1+4+5+28+9=47

How well does this work for you?