11 Replies Latest reply on Jun 28, 2016 11:50 AM by Diogo Braga

Conditional format sum positive numbers (ignore negative).

I have the following enrollment data organized by grade-level (columns). I need to calculate school-wide open seats, but can't do a regular roll up since there are negative and positive numbers. In this situation, when open seats are negative, it means the grade is over enrolled and it shouldn't be factored in the school-wide open seats calculation. In this example, we need to sum only the positive numbers (show in red). 45 + 1 + 8 + 13 + 8 + 11 + 9 = 95

After that I also need to add the pipeline numbers below (all positive), but only add the numbers from the squares where the open seats are red.

How do I do that?

• 1. Re: Conditional format sum positive numbers (ignore negative).

If you can share a sample workbook I can help you create a calculated field that you can use for the sum while still displaying all the values.

2 of 2 people found this helpful
• 2. Re: Conditional format sum positive numbers (ignore negative).

You can do "IF" logic inside your aggregates.  (Or inside your table calcs.  Or inside your LODs.)

SUM( IF (whatever) then [field you want to sum] END )

Khalid will show you how to do it if you post a workbook, but I thought I would put this out there for you so you can try it on your own first if you want.

1 of 1 people found this helpful
• 3. Re: Conditional format sum positive numbers (ignore negative).

Hey Joe,

I have this calculated field...

IF SUM([Open Seats]) > 0 THEN SUM([Open Seats]) END

But it still summing all, but displaying only 'Open Seats' greater than 0. I really want it to SUM only the positive numbers.

How should I tweak the formula for my purpose?

• 4. Re: Conditional format sum positive numbers (ignore negative).

It's kind of situational based on your application, but you want that to look something like this:

SUM(IF SUM([Open Seats]) > 0 THEN then SUM([open Seats]) END)

But even that's not right.  You can do (SUM(SUM...)  You probably want the operation to be WINDOW_SUM( IF ...)

That's why the request is that you post a sample workbook.  Depending on what you need to do (and where you need to do it), there are different ways to get there.

1 of 1 people found this helpful
• 5. Re: Conditional format sum positive numbers (ignore negative).

Hey khalid norat, I added the workbook. Thank you!

• 7. Re: Conditional format sum positive numbers (ignore negative).

Sorry to jump into this.

To add the pipeline you can try something like this.

Pipeline Total: 10+5+3+5+8 = 31

Let me know If this help.

Mahfooj

2 of 2 people found this helpful
• 8. Re: Conditional format sum positive numbers (ignore negative).

Hey Diogo Braga , I got a little tied up yesterday but Mahfooj came to the rescue, his solution is working for me. if it is not what you are looking for please mention how this is different to what you require and we can try getting to the required solution

• 9. Re: Conditional format sum positive numbers (ignore negative).

Mahfooj Khan wrote:

Sorry to jump into this. ...

Never apologize for jumping in!!

That's what this forum is all about.  I couldn't get back to this yesterday.  Khalid noted the same thing.  Someone else jumping in to finish off the solution is the way this should work!

And yes, Mahfooj, that's how I would have approached it as well.

2 of 2 people found this helpful
• 10. Re: Conditional format sum positive numbers (ignore negative).

I Agree. Never apologise for jumping in. We are all here to help. It takes the pressure of, knowing if we can't get back to someone cos we are tied up someone else is always going to jump in and finish up. Its all about Teamwork.

2 of 2 people found this helpful
• 11. Re: Conditional format sum positive numbers (ignore negative).

Thank you all for the help. Mahfooj Khan proposed solution didn't work for me.

In the Overview by Grade tab, if you remove the Grade Level pill from column, you will see that Positive Open Seat CF (copy) still factors in the negative number in the calculation. Going back to the example I gave, it shows 85, where it should show 95.

Pipeline is also not showing the correct numbers.

How can we fix this? Thank you!