subtotal a range of data if there's a value in a cell in another coulmn

[sneaky911]

I'm looking to get the Month End totals for week to date but only if there is a value in the Expense field (column 3) In the example provided I would like the Month End row to only calculate for weeks 1-3 until the expense for week 4 is entered. I've added a duplicate Month End row to show what the formula should return. Date Orders Expense 01/04/16 155 <colgroup><col></colgroup><tbody> </tbody> 1/05/16 291 01/06/16 218 01/07/16 168 01/08/16 233 01/09/16 97 01/10/16 55 Week 1 Total 1217 $ 74,874 <colgroup><col></colgroup><tbody> </tbody> 01/11/16 232 01/12/16 196 01/13/16 247 01/14/16 218 01/15/16 152 01/16/16 54 01/17/16 84 Week 2 Total 1183 $ 64,319 <colgroup><col></colgroup><tbody> </tbody> 01/18/16 125 01/19/16 233 01/20/16 141 01/21/16 154 01/22/16 160 01/23/16 291 01/24/16 107 Week 3 total 1211 $ 68,492 <colgroup><col></colgroup><tbody> </tbody> 01/25/16 211 01/26/16 91 01/27/16 180 01/28/16 106 01/29/16 193 01/30/16 50 01/31/16 54 Week 4 Total 885 Month End Total Month End Total 3611 $ 207,685 <colgroup><col></colgroup><tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[sneaky911]

never mind! solved my own problem.

 

[FDibbins]

what was your solution for this?

 

[sneaky911]

=sum(if(c9<>"",b2:b8,0))+sum(if(c17<>"",b10:b16,0))+sum(if(c25<>"",b18:b24,0))+sum(if(c33<>"",b26:b32,0))

 

[FDibbins]

How about just this?
=SUMIF(A:A,"WEEK*",C:C)

 

[sneaky911]

That one gives me a different result. Would I copy and paste exactly?

 

[FDibbins]

What did it give you?

below is tour table, with the rows deleted that do not contain an amount in column C (they have no affect on the total in your sample anyway)

	A​	B​	C​
1​	Date	Orders	Expense
2​	Week 1 Total	1217	$74,874
3​	Week 2 Total	1183	$64,319
4​	Week 3 total	1211	$68,492
5​	Week 4 Total	885
6​	Month End Total
7​	Month End Total	3611	$207,685
8​		207685​

B8=SUMIF(A:A,"week*",C:C)
V7 = your sample answer

 

[sneaky911]

Sorry...I probably worded this funny. I'm looking to return 3611 in B6. The expense actually populates from another tab in the file. 3611= Total cases for week 1-3 but not the 885 from week 4. (week 4 expense is unknown at this point) Once the week 4 expense is populated I then want week 4 added to the Month end total.

 

[FDibbins]

oops OK...
=SUMIFS(B:B,A:A,"week*",C:C,"<>")

 

[sneaky911]

That's the one! I originally started with the =sumifs but decided to take the long approach when I couldn't get it to work as expected. This is perfect. Appreciate your help!