Translate Excel formula into something meaningful?

A sample file would be easier to interpret as we can't see what is in cells C6 to E6 and whats on sheeet "Jan-19_Dem"

With that in mind though, the syntax of COUNTIFS is:

=COUNTIFS(Range1, Range1 criteria,Range2, Range2 Criteria,Range3, Range3 Criteria)

The INDIRECT is creating the range on the Jan-19_Dem sheet in column C but I think it is only looking at one cell so doesn't seem to make sense. The range is then being compared to values in D6 and E6

=COUNTIFS(

Range 1 INDIRECT("Jan-19_Dem!C"&MATCH($C6,'Jan-19_Dem'!$1:$1,0),FALSE),
Range 1 Criteria ">="&$D6,
Range 2 INDIRECT("Jan-19_Dem!C"&MATCH($C6,'Jan-19_Dem'!$1:$1,0),FALSE),
Range 2 criteria "<="&$E6)

I wanna cry i spent like 10 minutes trying to explain that in a resumed text, then clicked on submit and everything disapeared lol :(

ok but it was a good exercise to further resume what this does:

it searches for a value in row 1(match) and takes the resulting column (lets say column 7) and adds it to the letter C to form a cell reference (like C7) for example, then it takes whatever is inside C7 as a cell reference (that's what indirect does), so lets suppose C7 has the value R4C1 in it, indirect will return the contents of A4, then countifs will COUNT how many times the resulting value from above is greater or equal to the value in D6  and ALSO count how many times the resulting value from above is less than or equal to E6.

There is something that doesn't make sense to me, indirect is using FALSE parameter which means it will read the reference as "R1C1" style, yet it is using the string literal "C"  to form the first part of the reference which is "A1"  style (default), so it is not possible to read a R1C1 style reference if said reference does not start with the letter "R" (like "R2C3") as it would always return 0 (zero).

Thinking that maybe column C on "Jan-19_Dem" contains a list of Range Names maybe referring to data for particular months, the value in C6 will be found in row 1 and determine which range name is used.

I suspect D6 and E6 are dates so the count is looking for entries that are between the two dates (inclusive).

@arana - I believe the INDIRECT will end up with a number after the C based on the MATCH, for example if the MATCH returns 7 the INDIRECT will be:

=INDIRECT("Jan-19_Dem!C7",FALSE)   so it will read the contents of C7 and evaluate as an R1C1 style reference.

With a bit of testing, the contents of the INDIRECT referred cell, C7 in my example, can contain a defined range name, so I go back to my earlier comment about column C containing a list of range names.

Thanks, guys, a friend asked me this question, as it is a formula in a report that generates a bunch of statistics for his business.  I was unable to get a copy of the spreadsheet, but will be able to dig under the hood tomorrow morning.

Dale

Actually, =INDIRECT("Jan-19_Dem!C7",FALSE) will interpret "Jan-19_Dem!C7" as an R1C1 reference, which means it refers to column 7 on that sheet, i.e. it's the equivalent of =INDIRECT("Jan-19_Dem!$G:$G") in A1 format.

@Rory - that makes sense. C6 is presumably a month reference which is then found in row 1 to determine which column is used for the count and then D6 and E6 are dates which would presumably occur in that column.

So, the Match function is supposed to look for the value "FPL_Percentage" (the value of Cell C6 in the active  worksheet), in row 1 of the "Jan-19_Dem" worksheet.  The match function is returning the column header (4), which is correct.

But the INDIRECT function is then looks like:

=INDIRECT("Jan-19_Dem!C4",FALSE)

Select allOpen in new window

Which is returning a #Ref error.

Which column/cell in that worksheet is this actually referring to, and why is it returning an error?

Perfect, thanks, Rory.

Now, if that worksheet name were "Jan_19_Dem", could I still use the single quotes as shown below?

=INDIRECT("'Jan_19_Dem'!C4",FALSE)

Select allOpen in new window

A friend inherited this workbook, and received very few instructions.  What is happening, is that one of the macros in this workbook, copies a formula that looks like:

=COUNTIFS(INDIRECT("BLANK!C"&MATCH($C5,BLANK!$1:$1,0),FALSE),">="&$D5,INDIRECT("BLANK!C"&MATCH($C5,BLANK!$1:$1,0),FALSE),"<="&$E5)

Select allOpen in new window

and uses a replace function to replace "Blank" with the value "Jan-19_Dem", where the user enters the "Jan-19" in an inputbox.  But I think we could just as easily use replace like:

Replace(strFunction, "Blank", chr$(39) & strMonth & chr$(39))

Select allOpen in new window

So, to answer the initial question with the variables clearer:

C6 - Account Type (?)  - in sample = FPL_Percentage
D6 - Start Date, First of Month(?)
E6 - End Date, Last of Month(?)

I am assuming the layout of the Jan-19 sheet is row 1 with Account Type in the first column of each of multiple areas. Each area (at least two columns per area) then has dates in the first column and the relevant values against those dates in subsequent columns.

FPL_Percentage                           Another Account
Date     Value 1     Value 2           Date     Value 1     Value 2

Formula finds the account type in row 1 of the Jan-19 sheet and counts that column for the number of entries between the two dates.

Yes, it never does any harm to add the quotes and will cater for all sheet names.

you could still use single quotes but you would not need them in that case, so better safe than sorry, keep them

Thanks, guys, for the help.