Somebody pls help me!!

gresple · May 1st, 2002, 10:16 AM

A B C
1 Full Planned 150
2 Full Actual 148
3 Part Planned 50
4 Part Actual 39
5 Full Planned 150
6 Full Actual 162

How can I have the sum of "Full" "Actual" that equals 310?

NateO · May 1st, 2002, 10:23 AM

Assuming that the numbers are in column A and "Full", etc...are in column B, enter the following formula in the formula bar and press ctrl-shift-enter (same time):

Code:

=SUM((B1:B6="Full")*(C1:C6="Actual")*D1:D6)

Hope this helps.

Dave Patton · May 1st, 2002, 10:26 AM

Edited (information in 2 columns)

310 =SUMPRODUCT((A1:A6="Full")*(B1:B6="Actual")*(C1:C6))
310 =SUMPRODUCT((A1:A6=F1)*(B1:B6=G1)*(C1:C6))

look at Sumif in Help

310 =SUMIF(B1:B6,"Full Actual",C1:C6)

with criteria in E1
310 =SUMIF(B1:B6,E1,C1:C6)

Revise cell references as necessary

[ This Message was edited by: Dave Patton on 2002-05-01 09:31 ]

NateO · May 1st, 2002, 10:29 AM

If 1 & Full are in the same cell use the following in the formula bar:

Code:

=SUM((MID(A1:A6,3,4)="Full")*(B1:B6="Actual")*C1:C6)

And press ctrl-shift-enter at the same time.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-01 09:44 ]

gresple · May 2nd, 2002, 10:52 AM

Thank You guys!!

May 1st, 2002, 10:23 AM	#2
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Assuming that the numbers are in column A and "Full", etc...are in column B, enter the following formula in the formula bar and press ctrl-shift-enter (same time): Code: =SUM((B1:B6="Full")(C1:C6="Actual")D1:D6) Hope this helps. __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

May 1st, 2002, 10:29 AM	#4
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	If 1 & Full are in the same cell use the following in the formula bar: Code: =SUM((MID(A1:A6,3,4)="Full")(B1:B6="Actual")C1:C6) And press ctrl-shift-enter at the same time. _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-05-01 09:44 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 1st, 2002, 10:16 AM	#1
gresple New Member Join Date: Apr 2002 Location: Hong Kong Posts: 5	A B C 1 Full Planned 150 2 Full Actual 148 3 Part Planned 50 4 Part Actual 39 5 Full Planned 150 6 Full Actual 162 How can I have the sum of "Full" "Actual" that equals 310?

May 1st, 2002, 10:26 AM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Edited (information in 2 columns) 310 =SUMPRODUCT((A1:A6="Full")(B1:B6="Actual")(C1:C6)) 310 =SUMPRODUCT((A1:A6=F1)(B1:B6=G1)(C1:C6)) look at Sumif in Help 310 =SUMIF(B1:B6,"Full Actual",C1:C6) with criteria in E1 310 =SUMIF(B1:B6,E1,C1:C6) Revise cell references as necessary [ This Message was edited by: Dave Patton on 2002-05-01 09:31 ]

May 2nd, 2002, 10:52 AM	#5
gresple New Member Join Date: Apr 2002 Location: Hong Kong Posts: 5	Thank You guys!!