Using subtotal & filters, but how to subtract a certain row within the filter?

[vgupta23]

Hey everyone,

Hope you can help. Let us say this is my data:

[TABLE="width: 300"]
<TBODY>[TR]
[TD]MONDAY </SPAN>[/TD]
[TD]MORNING</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[/TR]
[TR]
[TD]MONDAY </SPAN>[/TD]
[TD]DAY</SPAN>[/TD]
[TD]200</SPAN>[/TD]
[/TR]
[TR]
[TD]MONDAY </SPAN>[/TD]
[TD]NIGHT</SPAN>[/TD]
[TD]400</SPAN>[/TD]
[/TR]
[TR]
[TD]TUESDAY</SPAN>[/TD]
[TD]MORNING</SPAN>[/TD]
[TD]300</SPAN>[/TD]
[/TR]
[TR]
[TD]TUESDAY</SPAN>[/TD]
[TD]DAY</SPAN>[/TD]
[TD]300</SPAN>[/TD]
[/TR]
[TR]
[TD]TUESDAY</SPAN>[/TD]
[TD]NIGHT</SPAN>[/TD]
[TD]200</SPAN>[/TD]
[/TR]
[TR]
[TD]WEDNESDAY</SPAN>[/TD]
[TD]MORNING</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[/TR]
[TR]
[TD]WEDNESDAY</SPAN>[/TD]
[TD]DAY</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[/TR]
[TR]
[TD]WEDNESDAY</SPAN>[/TD]
[TD]NIGHT</SPAN>[/TD]
[TD]400</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

So if I use the subtotal formula, and filter for only monday, it will sum my total revenue for morning, day, and night. When i use subtotal and filter for Monday, it will return $700. if i use =SUBTOTAL(9, C1:C9) - SUMIF(B1:B9, "NIGHT", C1:C9), then answer will be $700-($1000) = -$300.

How do i filter for monday, but only subtotal for revenue in the morning and day, but get rid of night? total should be $600 .. ?

Hopefully some can answer ... thanks


Vee