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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
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
54,456
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
46,227
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,913
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top