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

Sum of negative values for variable number of columns

T

Thomas Kuriakose

Active Member
Respected Sirs,

Kindly find attached a file with data where we need to sum only the negative values before the Totals column. The number of columns with positive and negative data keep varying.

I tried sumif, but the moment I delete or add columns with data the reference does not change.

Kindly check and let me know how to get the sum of negative values when the number of columns keep varying.

Thank,

with regards,
thomas
 

Attachments

  • Test 1.xlsx
    10.7 KB · Views: 10
Hi Thomas,

In such cases, taking the help form Dynamic named ranges helps us.

I have created a dynamic named range named VarianceValues which refers to all values except row header and total.

Once the same defined, I am using this named range in my SUMIF function.

Hope this helps.

Thanks,
Ramesh Kumar.P
 

Attachments

  • Test 1.xlsx
    11 KB · Views: 7
Respected Sir,

Thank you for this information and solution.

However, in our case the number of columns at the start is not known.

For example, in this example, the number of columns at the beginning is 1, and the sumif is entered in B4. Then the user inserts 5 columns and inputs values in the 5 columns.

The sum value in B4 should then consider only 5 column values leaving the total column. And this can be 3 columns, 10 columns , 50 columns and so on.

How can we use dynamic columns without knowing the ranges.

Thanks you so much once again.

with regards,
thomas
 
Based on your feedback given above, I have created multiple scenarios and have summed up the negative values in each scenario.

Please refer to the attached updated template and see if this helps..
 

Attachments

  • Test 1.xlsx
    12.5 KB · Views: 9

Hi !

FWIW, Thomas, if Total can't be negative : =SUMIF(2:2,"<0") …
Seems far too easy !
​
 
Respected Sirs,

This was an old post to sum only negative values for variable number of columns for which all the solutions provided worked perfectly.

Kindly help with a revised formula for the variable columns if only the total's column is negative.

Thank you very much once again,

with regards,
thomas
 

Attachments

  • Total Negative.xlsx
    13.3 KB · Views: 3
Respected Sirs,

Thank you very much for the revised formula. Both solutions worked.

Much much appreciated,

Thanks once again,

with regards,
Thomas
 
Respected Sirs,

Thank you very much for this update and revision without defined name.

Sir, my manager needs another change. Now we need the total value in the sum of negatives for the variable columns.

Kindly find the required value in blue.

Thank you very much,

with regards,
thomas
 

Attachments

  • Copy of Total Negative(1).xlsx
    13.5 KB · Views: 5
Hello Thomas,

Try these.

Sum of -ve:

=SUMIFS(3:3,2:2,"<>Total",3:3,"<0")*(LOOKUP(99^99,3:3)<0)

For Total;

=LOOKUP(99^99,3:3)*(LOOKUP(99^99,3:3)<0)

Edit:

or with MIN,
=MIN(0,LOOKUP(99^99,3:3))
 
Last edited:
Thomas Kuriakose said:
Respected Sirs,

Thank you very much for this update and revision without defined name.

Sir, my manager needs another change. Now we need the total value in the sum of negatives for the variable columns.

Kindly find the required value in blue.

Thank you very much,

with regards,
thomas
Click to expand...
B7,

=IF(B5<0,LOOKUP(9^9,3:3),0)

Regards
Bosco
 

Attachments

  • Total Negative(2).xlsx
    13.7 KB · Views: 3
You must log in or register to reply here.
Back
Top