Google sheets Using the return from a match function within another function

Question

I have been trying to use the MATCH function in google sheets in order to get the row of when a certain date ends in a column of dates. The date for example looks like this

           Col 1      Col 2      Col 3     Col 4   
row 1      8-24-2021  Banana     20        6
row 2      8-24-2021  Apple      20        10
row 3      8-25-2021  Banana     20        8
row n      ...
row 300    8-31-2021  Orange     20        8

So for example I want to return when row # of when 8-25-2021 starts which would be row 1 because row 3 has 8-25-2021. THen use that same row later on in a function such as this: =countif(sheet2!$G$X:$G, D3) where X is the cell that contains the return from the match function.

Now the reason why the countif function would be calling from a separate cell is that I want to be able to easily change the date that I am matching for using two other cells like this for example:

      A                          B                                     C
1     8-24-2021                =match(A1,RefsSent!$J$2:$J,0)+1         Start Date
2     8-31-2021                =match(A2,RefsSent!$J$2:$J,0)+1         End Date
3

This would enable me to change the two dates easily to use later in the count function. I have tried the following =countif(sheet2!$G$&A1&:$G$&A2&,D3) and =countif(sheet2!$G$(=match(A1,RefsSent!$J$2:$J,0)+1):$G$(=match(A2,RefsSent!$J$2:$J,0)+1),D3)

Is what I am asking for possible with the interactable cell or do I just have to create a countifs() and search for the date that way?

player0 player0 · Accepted Answer · 2021-09-01T23:24:07

what you need is ADDRESS

=COUNTIF(INDIRECT(sheet2!"&
 ADDRESS(MATCH(A1, RefsSent!J:J, ), COLUMN(G1))&":"&
 ADDRESS(MATCH(A2, RefsSent!J:J, ), COLUMN(G1))), D3)

Google sheets Using the return from a match function within another function

1 Answers