Complex Sum question

steallan

Active Member
Joined
Oct 20, 2004
Messages
304
Hi
Is it possible to do the following, some code i can write in the cell would be good.
I want to sum all values in row 6. But I want to only include cells in this sum if there is a specific word in row 2 of their column.

So ive got a sum, a value in say cell b6, that is only included in this sum if the word PLANNED is in b2

Can this be done?

:rolleyes:
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
515
would this work?


=IF(B2="planned",SUM(D6:J6),"")


N
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

if the formula goes to other than on row 2 or row 6 then try

Code:
=SUMIF(2:2,"PLANNED",6:6)

if you place this formula anywhere on row 2 or row 6, it will have circulate refrerence. otherwise you must specify the range like A2:Z2,"PLANNED",A6:Z6

hope this helps

jindon
 

steallan

Active Member
Joined
Oct 20, 2004
Messages
304
Hi
Sorry didnt explain my sheet properly. Say column 1 has the word Planned in row 2, column 2 has the word Actual in row 2, column three has Planned again, column four has Actual, etc etc
So I need the formula to only add the value to the sum if Planned is the word in row 2 of that column,
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848

ADVERTISEMENT

Copy the formula shown in the picture below, pay attention to suit value and condition ranges.
Cartel1
ABCDEFGHIJKL
1
2PlannedPlannedMATR.SOMMA.PRODOTTOisSUMPRODUCT
3
46NUMisN
5
6224
7
Foglio2


(y)
 

steallan

Active Member
Joined
Oct 20, 2004
Messages
304
Hi
Thanks for your help mate. Have just discovered the SUMIF fucnction in the help file and it does exacty what i wanted. Should have really done that before i posted.
Thanks Again
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
You are welcome :wink:

Remember this solution in case you have more than one condition

(y)
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
You are welcome :wink:

Remember this solution in case you have more than one condition

(y)
 

Forum statistics

Threads
1,147,507
Messages
5,741,564
Members
423,667
Latest member
Kai_357

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