Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing based on Filters - Excel 2007

    Howdy,

    I have an accommodation management spreadsheet which I use to:
    -book accommodation
    -categorise the stay
    -sum the charges based on category of stay.
    - sum the number of stays based on the category

    I can use the subtotal count function (F12) to count the number of stays based on the filter (typically date), however I cant work out how to count the number of nights for each individual category, eg "Non-Billable", "Private", or "Billable Special" (F13 to F17).

    Any help would be greatly appreciated.

    Thanks Claude
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Do you mean if you filtered on, say, QUEEN and non-billable, you want to know how many show up?

    If so, in F13, try: =SUBTOTAL(3,H24:H186) where 3 is COUNTA

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts
    what i am after, is that if I filter on a date range, eg February, then I would like to know how many nights were billable, non-billable, and billable special.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    In F13, try: =SUMPRODUCT(SUBTOTAL(3,OFFSET(H24,ROW(H24:H186)-ROW(H24),0)),(H24:H186="Non-Billable")+0)


    And, change the "type" accordingly for the other cells. Is this it?

    Kevin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •