4

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.

enter image description here

1 Answer 1

5
  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
      Custom formula is
      (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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.