SUMIFS question to grab negative numbers in a column

[Hazra Hadee]

Hello,

I am trying to grab total from a table for all contracts that increase in price, decrease and has no change. I did the increase part using SUMIFS. Not sure how to write sumifs to grab negative total and zero values from the column. Attaching a sample file here. Thank you in advance.

 

[Hazra Hadee]

I have an update on my question. My formula for the increase doesn't work correctly, I just realized that it totals the whole column which also has the negative numbers! So I do need help on getting the positive numbers also from the column using SUMIFS or a formula! Thank you!

 

[pecoflyer]

Negative In B6 and drag right =SUMIFS($M$4:$M$6,$L$4:$L$6,"Yes",$I$4:$I$6,$A$4,$H$4:$H$6,B$3,$M$4:$M$6,"<0")
Replace the last condtion with ">0" or 0 or the other values

 

[Fluff13]

How about
=SUMIFS(M4:M6,L4:L6,"Yes",I4:I6,A4,H4:H6,B3,M4:M6,">0")

Just change the > to < for -ve numbers & to = for no change

 

[Hazra Hadee]

Sorry for the bother. But I figured it out. Used SUMIFS and >o, <0 option!

 

[Hazra Hadee]

How about
=SUMIFS(M4:M6,L4:L6,"Yes",I4:I6,A4,H4:H6,B3,M4:M6,">0")

Just change the > to < for -ve numbers & to = for no change

Thank you for your help! Much appreciated!

 

[Hazra Hadee]

Negative In B6 and drag right =SUMIFS($M$4:$M$6,$L$4:$L$6,"Yes",$I$4:$I$6,$A$4,$H$4:$H$6,B$3,$M$4:$M$6,"<0")
Replace the last condtion with ">0" or 0 or the other values

This is a great option! Thank you for your help! Much appreciated!

 

[Fluff13]

Glad we could help & thanks for the feedback.

 

[Peter Bartholomew]

The formula
= SUMIFS(Change,
FundNo., fund,
GLCode, code,
ContractRenewed?, "Yes",
Change, {">0";"<0";0})
returns all three rows for each contract as a single array formula.

 

[Hazra Hadee]

Thank you! This is even better! Thanks a million!

 

[Hazra Hadee]

The formula
= SUMIFS(Change,
FundNo., fund,
GLCode, code,
ContractRenewed?, "Yes",
Change, {">0";"<0";0})
returns all three rows for each contract as a single array formula.
View attachment 68841

Can you please post the file you entered the formula in. Thank you

 

[Hazra Hadee]

The formula
= SUMIFS(Change,
FundNo., fund,
GLCode, code,
ContractRenewed?, "Yes",
Change, {">0";"<0";0})
returns all three rows for each contract as a single array formula.
View attachment 68841

I entered the above formula, but getting a #Name? error! Don't see any misspellings! or I am going blind! Please help. Thank you

 

[Peter Bartholomew]

The file was written to be convenient for dynamic array versions of Excel (Office 365).
It should work in other versions but CSE always was a pain.

As for #Name? errors you need to evaluate the formula either by using the button on the Formulas ribbon tab or F9 in the formula bar to determine which Names are undefined.

 

[Chihiro]

You can always nest SUMIFS inside INDEX function and use ROWS($A$1:A1) & COLUMNS($A$1:A1) to drag across and down.

Ex:
= INDEX(SUMIFS(Change,FundNo., fund,GLCode, code,ContractRenewed?, "Yes",Change, {">0";"<0";0}),ROWS($A$1:A1),COLUMNS($A$1:A1))

 

[Hazra Hadee]

The file was written to be convenient for dynamic array versions of Excel (Office 365).
It should work in other versions but CSE always was a pain.

As for #Name? errors you need to evaluate the formula either by using the button on the Formulas ribbon tab or F9 in the formula bar to determine which Names are undefined.

I did try that. Thank you for the file.

 

[Hazra Hadee]

You can always nest SUMIFS inside INDEX function and use ROWS($A$1:A1) & COLUMNS($A$1:A1) to drag across and down.

Ex:
= INDEX(SUMIFS(Change,FundNo., fund,GLCode, code,ContractRenewed?, "Yes",Change, {">0";"<0";0}),ROWS($A$1:A1),COLUMNS($A$1:A1))

Thank you. Let me try this and will update