excel - Return address/info in address for CountIfs function?

Question

I have an Excel file with a large amount of COUNTIFS functions. The formulas work fine but I need to be able to find a way to return not only the address for each COUNTIFS, but also what is in the cell.

I created a simplified worksheet with what the data set looks like. My countifs function looks something like this:

=COUNTIFS(A$3:A$7,"="&G3,B$3:B$7,">="&H3,C$3:C$7,"<="&I3)

I have looked at similar problems on here and tried two user defined functions:

Public Function ListAddresses(SearchTerm As Variant, SearchRange As Range) As String
    Dim WS As Worksheet, rCell As Range

    Set WS = Sheets(SearchRange.Parent.Name)
    SearchTerm = UCase(SearchTerm)

    Set SearchRange = Intersect(WS.UsedRange, SearchRange)

    For Each rCell In SearchRange.Cells
        If UCase(rCell.Value) = SearchTerm Then
            ListAddresses = ListAddresses & rCell.Address(False, False) & " | "
        End If

    Next rCell

    If ListAddresses <> "" Then
        ListAddresses = Left(ListAddresses, Len(ListAddresses) - 3)
    Else
        ListAddresses = "<none>"
    End If

and

Public Function CountIfFinder(rng As Range, crit As String) As String
    Dim r As Range, DQ As String

    DQ = Chr(34)
    crit = DQ & crit & DQ
    CountIfFinder = ""
    Set rng = Intersect(rng, rng.Parent.UsedRange)

    For Each r In rng
        s = "=countif(" & r.Address & "," & crit & ")"
        If Evaluate(s) = 1 Then CountIfFinder = CountIfFinder & "," & r.Address(0, 0)
    Next r

    CountIfFinder = Mid(CountIfFinder, 2)

Any help would be appreciated because as of now I have to search through the DapSeq peaks by hand to find the peaks that match and there are almost 118,000 of them (yikes).

The question marks are the info from the addresses that I would need the function(s) to return.

question from:https://stackoverflow.com/questions/65946611/return-address-info-in-address-for-countifs-function

Answer 1

Waitting for answers