Complex Sum question

[steallan]

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?

 

[neilp]

would this work?


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


N

 

[jindon]

Hi

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

=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]

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]

Copy the formula shown in the picture below, pay attention to suit value and condition ranges.

Cartel1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Planned		Planned			MATR.SOMMA.PRODOTTOisSUMPRODUCT
3
4					6		NUMisN
5
6		2	2	4
7
Foglio2

 

[steallan]

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]

You are welcome :wink:

Remember this solution in case you have more than one condition

 

[chiello]

You are welcome :wink:

Remember this solution in case you have more than one condition