Filter and subtotal by month

Question

So I'm trying to do three things that are related to each other that I'm struggling to figure out:

1. I'd like to filter an entire column (B) based on a the month (let's say April), while maintaining the format of month/day/year. Is this possible?

2. Once I accomplish the above, I would like to be able to display subtotals based off of this filtering in my yellow (J26) and green (K26) cells. So if the filtering is done right, the J26, and K26 boxes should display subtotals for the month of April.

3. Then (I know I'm being a bit ambitious) I also want a complete separate sheet to reference subtotals for the month of April to help me estimate cash flow. Not sure if this is possible or not.

Answer 1

1. You don't want to filter the whole column if you need subtotals somewhere below the data. If you filtered the whole column, the subtotal row would be filtered out. So, to filter the data for April:

   1. Select your data table
   2. Data > Filter
   3. Small arrow by the header of the needed column > Filter by condition... > Custom formula is > enter this formula: =month(A2)=4 > then OK
      
      (My dates are in column A)

2. For subtotals just use the formula below your data like this: =subtotal(9,D2:D7)

3. In a separate sheet I would use a formula which is not dependent on the filter, like this:
   =sum(arrayformula(Sheet1!D2:D7*(month(Sheet1!A2:A7)=4)))
   If your sheet name consists of several words use commas when referencing it like this:
   'My data'!A2:A7