Round and SUMIF Question

Please post a sample file so there is data to work with: include expected results.

To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

My apologies files were not attached. Here are the two i was attempting to attached

Ex1.JPG




And

ex2.JPG

I am not sure if the attachment was attached Are you able to see it

In Column 1 i have the formula = 0 and in Column 2 i have the >-5<5 Formula.

i wanted the below formula in Column 1 only but it alway gave me errors so i split it up. between the two

if(ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]) ,2)=0,"Zero",if(and(ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)>-5,ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)<0),"Less 5",if(and(ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)>0,ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)<5),"between zero and 5","Not between -5 and 5")

So in the data Look at lines 87 to 96. They tie to zero and were overlook. Should this formula possible need to be split in two Could that be the reason. I dont know if this helps but All debits/Positive has the word "WCR" in Description column that should tie to Zero with credits by 1 to 1 or 1 to many. I dont think that matters really but may help in logic. Because ultimately i am trying to try all wcr which are Invoice to Deposit that are all credits

Would it be better to separate the debits from the credits and then run the formulas to say per this debit find the credits that total up to be zero.

=ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)

What this is doing is summing EVERY amount where Complex CC is the same as that line.

To make this clear:
a 1
a 2
a 3
a 4
a 5
b 6
b 7
b 8
b 9

would display
15
15
15
15
15
30
30
30
30

(1 + 2 + 3 + 4 + 5 = 15)
(6+7+8+9 = 30)

Does that make sense?

If I add this to a new column as a sanity check

=ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)

Then this to a new column

=IF(ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)=0,"Zero",IF(AND(ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)>-5,ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)<0),"Less 5",IF(AND(ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)>0,ROUND(SUMIFS([Amount],[Complex CC],[@[Complex CC]]),2)<5),"between zero and 5","Not between -5 and 5")))

It shows "Zero" for zero and "Not between -5 and 5" for everything else (which it should, because nothing else is between those numbers...)

The formula works for the criteria you have defined... are you defining the right criteria?

Ok I see what you are saying. I think i have the wrong criteria. I have been meaning for it to say, per the common [Complex CC], Per each amount in that common [complex cc], sum with the other amounts in that common [complex cc] to find a sum of 0 and or =>-5 or =<5. And label those. Which is slightly different than the other criteria.

For instance row 359 to 376. Event though it is 18 lines totaling to 28.45 there are some values in the amount that sum up to Zero if the criteria where to search each amount by the Common [Complex CC] and then sum and label the zero or +\- <> of 5's than this would be the correct criteria to use.

How would that change the previous formula? I totally understand what you are saying now
And to follow up on your example which was great
Yes
I need it to take the 1 and 3 of the group and sum it and say it is less than 5 and give me that reply or if the sum equals to zero etc

After thinking wouldn't it possible be easier to split the debit and credits up like in the VSE tab and then run a logic in Column H- Per the common Complex look in Column G and sum in all that ties to zero and label zero. etc OR would this be better to run this as a macro. First saying find all values per common [Complex] that tie zero and then remove and place in a new tabe and then re sum and find all that are >-5 place in that same tab with the zero ,re sum and find all that is <5 and place in the same tab with zero finish and go to the next value