Need help with sum if

pkohli

Board Regular
Joined
Aug 2, 2005
Messages
71
Hi

I have a data in the following order:-
Book3
ABCDE
2FY06FY07FY08
3CostIdentified10000020000034000
4CostProposed34500345000870000
5RevenueImplemented45000500086000
6CostApproved670007600088000
7RevenueProposed7600088000056000
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,965
Office Version
  1. 365
Platform
  1. Windows
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.
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Code:
=SUMPRODUCT((A3:A7="Cost")*(B3:B7="Identified")*(C3:C7))

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,965
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,585
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Aug 2, 2005
Messages
71
let me extend the data:-
Book4
ABCDE
1FY06FY07FY08
2CostIdentified10000020000034000
3CostIdentified3000450006000
4CostProposed34500345000870000
5RevenueImplemented45000500086000
6CostApproved670007600088000
7CostIdentified670005400087000
8RevenueProposed7600088000056000
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.
 

Forum statistics

Threads
1,141,226
Messages
5,705,130
Members
421,379
Latest member
SoundlessResonance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top