Subtotal + sumif?

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]
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi @★ DENISEKELLY, welcome to the forum!

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:154.93px;" /><col style="width:130.22px;" /><col style="width:118.81px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >TEAM</td><td >REGION</td><td >MONTH</td><td >COST</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >OPS</td><td >ASIA</td><td >JANUARY</td><td style="text-align:right; ">$18,000.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >SALES</td><td >EUROPE</td><td >FEBRUARY</td><td style="text-align:right; ">$6,000.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >CFO</td><td >ASIA</td><td >JANUARY</td><td style="text-align:right; ">$15,000.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td >JANUARY SPEND</td><td style="text-align:right; ">$33,000.00</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td >FEBRUARY SPEND</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td >MARCH SPEND</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C21</td><td >=SUMPRODUCT(SUBTOTAL(109,OFFSET($D$2,ROW($D$2:$D$15)-ROW($D$2),,1))*(C2:C15="JANUARY"))</td></tr></table></td></tr></table>
 
Last edited:
Upvote 0
You could take the name of the month in column C, then it would look like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:154.93px;" /><col style="width:130.22px;" /><col style="width:118.81px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >TEAM</td><td >REGION</td><td >MONTH</td><td >COST</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >OPS</td><td >ASIA</td><td >JANUARY</td><td style="text-align:right; ">$18,000.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >SALES</td><td >EUROPE</td><td >FEBRUARY</td><td style="text-align:right; ">$6,000.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >CFO</td><td >ASIA</td><td >JANUARY</td><td style="text-align:right; ">$15,000.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td > </td><td >JANUARY</td><td style="text-align:right; ">$33,000.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td><td >FEBRUARY</td><td style="text-align:right; ">$6,000.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td > </td><td >MARCH</td><td style="text-align:right; ">$0.00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D21</td><td >=SUMPRODUCT(SUBTOTAL(109,OFFSET($D$2,ROW($D$2:$D$15)-ROW($D$2),,1))*($C$2:$C$15=C21))</td></tr></table></td></tr></table>
 
Upvote 0
Can you advise what the different syntax are doing? I have a very large set of data applying a SUMIF formula to. I would like to be able to leverage the SUBTOTAL functionality. Why is "SUBTOTAL (109," required? What is the difference between 109 & 9? I do not understand what is going on in the formula and not sure if it will work in my scenario. It will need to be revised and I want to make sure I change formula correctly to get the output I am looking for
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top