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

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

<tbody>
</tbody>

 

[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