DENISEKELLY
New Member
- Joined
- Oct 10, 2019
- Messages
- 3
Hello!
I have created this chart to track company travel for the year. I wanted the chart to be sortable so I could look at the number of trips a team, individual, etc. are taking each month and the costs associated.
Where I am getting stuck is the monthly totals formula. I need the monthly spend to act as subtotals, and disregard rows that are not visible when sorted.
If I were to imagine the formula to get determine my February spend it would be this: SUBTOTAL(SUMIF(F2:F18,"FEBRUARY",I2:I18)
Can you help me figure out this formula, assuming I have already sorted the table as such?
Thanks in advance!
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]TEAM
[/TD]
[TD="align: center"]REGION
[/TD]
[TD="align: center"]MONTH
[/TD]
[TD="align: center"]COST
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]OPS
[/TD]
[TD="align: center"]ASIA
[/TD]
[TD="align: center"]JANUARY
[/TD]
[TD="align: center"]$18000
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]SALES
[/TD]
[TD="align: center"]EUROPE
[/TD]
[TD="align: center"]FEBRUARY
[/TD]
[TD="align: center"]$6000
[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD="align: center"]CFO
[/TD]
[TD="align: center"]ASIA
[/TD]
[TD="align: center"]JANUARY
[/TD]
[TD="align: center"]$18000
[/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]JANUARY SPEND
[/TD]
[TD="align: center"]???
[/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]FEBRUARY SPEND
[/TD]
[TD="align: center"]???
[/TD]
[/TR]
[TR]
[TD="align: center"]20
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]MARCH SPEND
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have created this chart to track company travel for the year. I wanted the chart to be sortable so I could look at the number of trips a team, individual, etc. are taking each month and the costs associated.
Where I am getting stuck is the monthly totals formula. I need the monthly spend to act as subtotals, and disregard rows that are not visible when sorted.
If I were to imagine the formula to get determine my February spend it would be this: SUBTOTAL(SUMIF(F2:F18,"FEBRUARY",I2:I18)
Can you help me figure out this formula, assuming I have already sorted the table as such?
Thanks in advance!
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]TEAM
[/TD]
[TD="align: center"]REGION
[/TD]
[TD="align: center"]MONTH
[/TD]
[TD="align: center"]COST
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]OPS
[/TD]
[TD="align: center"]ASIA
[/TD]
[TD="align: center"]JANUARY
[/TD]
[TD="align: center"]$18000
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]SALES
[/TD]
[TD="align: center"]EUROPE
[/TD]
[TD="align: center"]FEBRUARY
[/TD]
[TD="align: center"]$6000
[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD="align: center"]CFO
[/TD]
[TD="align: center"]ASIA
[/TD]
[TD="align: center"]JANUARY
[/TD]
[TD="align: center"]$18000
[/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]JANUARY SPEND
[/TD]
[TD="align: center"]???
[/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]FEBRUARY SPEND
[/TD]
[TD="align: center"]???
[/TD]
[/TR]
[TR]
[TD="align: center"]20
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]MARCH SPEND
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]