12. Total of values per month on a filtered table



 PROBLEM:  How to count values per month in a filtered table?


=IFERROR(IF(SUM( IF(FREQUENCY(IF(SUBTOTAL(103;OFFSET($Q$22;ROW($Q$22:$Q$27)-ROW($Q$22);;1))*(MONTH($R$22:$R$27)=MONTH(O4))*($R$22:$R$27<>""); IF($Q$22:$Q$27<>""; MATCH("~"&$Q$22:$Q$27;$Q$22:$Q$27&"";0))); ROW($Q$22:$Q$27)-ROW($Q$22)+1);1))=0;"";SUM( IF(FREQUENCY(IF(SUBTOTAL(103;OFFSET($Q$22;ROW($Q$22:$Q$27)-ROW($Q$22);;1))*(MONTH($R$22:$R$27)=MONTH(O4))*($R$22:$R$27<>""); IF($Q$22:$Q$27<>""; MATCH("~"&$Q$22:$Q$27;$Q$22:$Q$27&"";0))); ROW($Q$22:$Q$27)-ROW($Q$22)+1);1)));"")

    This formula is a nested IFERROR, SUMPRODUCT, and SUBTOTAL function that returns the sum of the number of cells in the range Q22:Q27 where the value in the column DATE is in the same month as the current month and the value in the cell is not empty.

    If there are no such cells, it returns an empty string.

    Here’s how it works:

  1.  ROW($Q$22:$Q$27)-ROW($Q$22)+1 returns an array of numbers from 1 to 6.

  2.  OFFSET($Q$22;ROW($Q$22:$Q$27)-ROW($Q$22);;1) returns an array of cells in the range $Q$22:$Q$27.

  3.  SUBTOTAL(103;...) returns an array of subtotal values for each cell in the array returned by step 2.

  4.  (MONTH($R$22:$R$27)=MONTH(O9)) returns an array of TRUE or FALSE values where each value is TRUE if the month in the corresponding cell in the column DATE of the table SOURCE is equal to the current month.

  5.  ($R$22:$R$27<>"") returns an array of TRUE or FALSE values where each value is TRUE if the corresponding cell in the column R of the table SOURCE is not empty.

  6.  IF(...) returns an array of values where each value is either a match number or an empty string.

  7.  FREQUENCY(IF(...);1) returns an array of frequency counts for each unique value in the array returned by step 6.

  8.  SUM(...) returns the sum of the array returned by step 7, which is the number of cells in the range Q22:Q27 where the value in the column DATE is in the same month as the current month and the value in the cell is not empty.

  9.  The tilde symbol (~) in Excel is used as an escape character. It tells Excel to treat the next character in the formula as a literal character, rather than as a special character.

    For example, if you want to use an asterisk (*) in a formula, but you don’t want Excel to interpret it as a multiplication operator, you can precede it with a tilde symbol.

    So, instead of typing =A1*B1, you would type =A1~*B1. The tilde symbol is also used to escape other special characters in Excel formulas, such as the question mark (?), the left bracket ([), and the right bracket (]).

    In the formula provided, the tilde symbol (~) is used as an escape character to treat the next character in the formula as a literal character, rather than as a special character. In this case, the tilde symbol is used to escape the ampersand (&) character, which is a special character in Excel formulas.

    The formula provided is checking if the cells in the range Q22:Q27 are not empty. If a cell is not empty, it returns the result of the MATCH function.

    The MATCH function searches for the value of the cell in the range Q22:Q27 and returns the position of the first cell that matches the search value. The tilde symbol is used to escape the ampersand character in the formula.