Complex Sum question

steallan

Active Member
Joined
Oct 20, 2004
Messages
305
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
522
Office Version
  1. 365
Platform
  1. Windows
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
305
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
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
305
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,181,609
Messages
5,930,894
Members
436,765
Latest member
kan01

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