Need help with sum if

[pkohli]

Hi

I have a data in the following order:-

Book3
	A	B	C	D	E
2			FY06	FY07	FY08
3	Cost	Identified	100000	200000	34000
4	Cost	Proposed	34500	345000	870000
5	Revenue	Implemented	45000	5000	86000
6	Cost	Approved	67000	76000	88000
7	Revenue	Proposed	76000	880000	56000
8
Sheet1

I need to write a formula to sum i.e. sum the values of FY06-08, if column A is cost and column B is identified...could you please help in writing the formula???

Thanks
Pooja

 

[Joe4]

Try this for FY06's totals:

=SUMPRODUCT((A3:A7="Cost")*(B3:B7="Identified")*(C3:C7))

Repeat for your other two totals, simply changing C3:C7 to D3:D7 and E3:E7.

 

[pkohli]

not working (

 

[bsnapool]

=SUMPRODUCT((A3:A7="Cost")*(B3:B7="Identified")*(C3:C7))

I think you wouyld need to change C3:C7 > C3:E3

 

[Joe4]

I think you wouyld need to change C3:C7 > C3:E3

I guess that depends on whether you want to sum by column or by row. I was assuming column.

However, if they want to sum by row, then making that change to the formula will not work, as all ranges in a SUMPRODUCT must be the same size. Simply use:

=IF(AND(A3="Cost",B3="Identified",SUM(C3:E3),0)

 

[bsnapool]

I need to write a formula to sum i.e. sum the values of FY06-08,

I thought it meant column at first until i took a second look...

 

[Peter_SSs]

not working (

A little more than this is always helpful.

1. What result does the suggested formula give for you with your sample data?
2. What result do you expect for the sample data?

 

[pkohli]

I needto sum column not rows

 

[Peter_SSs]

I needto sum column not rows

What about my two questions?

 

[pkohli]

let me extend the data:-

Book4
	A	B	C	D	E
1			FY06	FY07	FY08
2	Cost	Identified	100000	200000	34000
3	Cost	Identified	3000	45000	6000
4	Cost	Proposed	34500	345000	870000
5	Revenue	Implemented	45000	5000	86000
6	Cost	Approved	67000	76000	88000
7	Cost	Identified	67000	54000	87000
8	Revenue	Proposed	76000	880000	56000
Sheet1

The formula shld return me the value =596000... I have sum(C2:E3,C7:E7), i.e. all the values corresponding the field with cost and status with identified.