Subtotal and sum if ?

[KevBarry]

Have tried (and googled) this before without success so here goes.

Rough example:
I have a number of projects with a filter applied at ROW 4, what is the formula in C2/C3 that will generate the totals when the filter is active?

Thanks in advance
Kev




[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Narrative[/TD]
[TD]Totals[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Income[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Expenditure[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Project #[/TD]
[TD]Narrative[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[TD]Income[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[TD]Expenditure[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]Income[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]Expenditure[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nishant94]

=sumproduct(subtotal(3,offset($b$6,row($b$6:$b$9)-row($b$6),,,))*($c$6:$c$9)*($b$6:$b$9=b3))

 

[KevBarry]

Thanks for your reply but I am confused by your $b$6 reference as that does not make any logical sense to me ??? would you not be looking at B2 for income or B3 for expenditure?

 

[Nishant94]

Sorry I had a different setup of cells in my sheet.

In C2 use this

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$5,ROW($B$5:$B$8)-ROW($B$5),,,))*($C$5:$C$8)*($B$5:$B$8=B2))

 

[Aladin Akyurek]

A little less expensive...

Book1
	A	B	C
1		Narrative	Totals
2		Income	3000
3		Expenditure	800
4	Project #	Narrative	Value
5	A	Income	1000
6	A	Expenditure	500
7	B	Income	2000
8	B	Expenditure	300
Sheet1

In C2 enter and copy down to C3:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$5,ROW($C$5:$C$200)-ROW($C$5),0)),--($B$5:$B$200=$B2))

 

[KevBarry]

Thanks guys, knew there would be a simple way