How do I count the number of times a value appears in a 2D array in excel while also having a date condition that varies by row

Question

I'm trying to create a function that calculates the number of times a specific text value appears in a 2D array, while also meeting a conditional that is unique to each row.

Essentially its just a column of dates increasing by 1 day as it extends down, and a row of text values that span 48 cells that correspond to each row date value. I want to be able to count the number of time a specific value appears in a cell in a certain day that is based on another cell that I manually change the date to see the values on that date.

The countif part in the 2D array based on its value is easy, but I can't figure out how to relate the conditions of a 1D array with that of a 2D.

Answer 1

Let's say: date you care about is in A1; value/text you want to find is in B1; list of dates is in A3:A1000; data is in B3:Z1000. Using MATCH you can find the row where the date matches, then use that inside an INDEX to return that whole row of data, then use that inside a COUNTIF to look for the text you want:

=COUNTIF(INDEX($B$3:$Z$1000,MATCH($A$1,$A$3:$A$1000,0),),$B$1)

Notice the INDEX has a second comma - the MATCH returns the row, but we need to specify a column as well, but by including the comma and omitting a value for it, we get ALL columns in that row. You could specify the exact number of columns here but that won't be as flexible if you have more or fewer columns in future.