Negative values sum

[andresf]

Hi,

Can someone please help with this

Oct YTD = Jan+Feb+Mar+Apr+...+Oct. How can I get the sum of only the negative values?

Code	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23		Oct YTD
AAA	100	200	-100	-200	500	-700	1000	1200	-600	-500

Next month, it will be the same logic but including november's value. How can I update the range automatically (Jan+Feb+Mar+...+Nov)?

Code	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23		Nov YTD
AAA	100	200	-100	-200	500	-700	1000	1200	-600	-500	-1000

Thanks in advance,

Edu

 

[awoohaw]

if you are using excel 2010 (maybe 2007) or higher (PLEASE update your profile what version you use).
Assuming AAA is in cell A2.

=SUM(if(B2:M2<0,B2:M2,0))

enter with CTRL-SHFT-ENTR.

Mr excel questions 68.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	CODE	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23		YTD
2	AAA	100	200	-100	-200	500	-700	1000	1200	-600	-500				-2100
3		100	200	-100	-200	500	-700	1000	1200	-600	-500	-1000			-3100
andresf

Cell Formulas
Range	Formula
C1:M1	C1	=EDATE(B1,1)
O2:O3	O2	=SUM(IF(B2:M2<0,B2:M2,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=SUMIFS(B2:M2,B2:M2,"<0")

 

[awoohaw]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=SUMIFS(B2:M2,B2:M2,"<0")

@Fluff , one day I will remember that TRUE FALSE results can be summed on their own! I I did not word that correctly, please ignore.

 

[Scott Huish]

=SUMIF(B2:M2,"<0")