• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIFS question to grab negative numbers in a column

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.
 

Attachments

  • Excel_Query.xlsx
    14.8 KB · Views: 6
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!
 
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
 
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.
68841
 
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.
 

Attachments

  • Excel_Query.xlsx
    11.3 KB · Views: 7
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))
 
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.
 
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
 
Back
Top