# Complex Sum question

#### steallan

##### Active Member
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?

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
would this work?

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

N

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

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,

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

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

You are welcome :wink:

Remember this solution in case you have more than one condition

You are welcome :wink:

Remember this solution in case you have more than one condition

Replies
2
Views
297
Replies
5
Views
451
Replies
4
Views
107
Replies
9
Views
486
Replies
6
Views
414

1,220,950
Messages
6,157,028
Members
451,392
Latest member
malcv

### 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.

### Which adblocker are you using?

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

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