Count how many times a cell is referenced in a table of ranges

[mcharg]

Hi there! I am looking to count the number of times a cell in the ‘Plot Table’ is referenced by a range listed in the ‘Range Table’. The example in the attached image gives an example of what I am trying to achieve. As there will potentially be a large number of ranges in the ‘Range Table’ I am looking for a scalable formula to go in the Plot Table that will work for a large number of ranges. I am a bit stuck as to how to make this work so any assistance would be greatly appreciated. Let me know if more detail is required.

Thanks!

 

[mrshl9898]

Can't give you a formula, but here's a VBA solution to do the plotting and get the answer.

Sub plot()

Dim sht As Worksheet
Dim LastRow As Long
Dim rownum As Long
Dim plotrow As Long
Dim plotcol As Long

Set sht = ActiveSheet
rownum = 18
LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row

Do Until rownum = LastRow + 1
plotrow = 3
    Do Until plotrow = 13
    plotcol = 2
        Do Until plotcol = 12
            If Cells(plotrow, 1) >= Cells(rownum, 4) And Cells(plotrow, 1) <= Cells(rownum, 5) And Cells(2, plotcol) >= Cells(rownum, 2) And Cells(2, plotcol) <= Cells(rownum, 3) Then
            Cells(plotrow, plotcol) = Cells(plotrow, plotcol) + 1
            End If
        plotcol = plotcol + 1
        Loop
    plotrow = plotrow + 1
    Loop
rownum = rownum + 1
Loop

End Sub

 

[mcharg]

Thank you! This works great! I was hoping to do the same with a formula though, as this may be slow with a large data set.