# Sumifs Labeling

1. ## Sumifs Labeling

I am not sure if this can be done nor where to start, need help with taking the formula below an once it finds the things that tie to zero label them in groups
So if it is 1 to many it will label that group with a "1" and then next maybe one to one and it labels it "2" . Basically i want to see the grouping of the things that are zero beside seeing it only as all 1 lump sum. Any ideas how to do this. The attachment has the items group by Colors. I just need it group by a Number or Letter or anything beside color.

=IF(ROUND(SUMIFS([Amount],[OPERATION/NAME],[@[OPERATION/NAME]]),2)=0,"Zero")  Register To Reply

2. ## Re: Sumifs Labeling

There is no straight forward way to accomplish this.

The only way is to generate all possible combinations of numbers in Column G and check their sum, if the sum hits 0 for any combination then the values are allotted a group number.

Is there any indication in your data you can suggest that can limit the number of possible combinations. As without any indication the amount of possible combinations will increase tremendously with increase in amount of data and thus will increase amount of processing time.  Register To Reply

3. ## Re: Sumifs Labeling

Would it help if i did not run the formula off the [Amount] and have it state to look at the [payment] sumif the value of the number 251.01 for example with the numbers in [Receipts] and then label them as a group. etc etc. Would that work?  Register To Reply

4. ## Re: Sumifs Labeling

No, you are not getting my point. Sumif function doesn't provide you the the numbers that match up to a particular sum. We will need to generate all possible combinations using VBA and check each combination if that equals 0. I was asking if there is any way we can limit the number of combinations looking at other columns. But I went through the data, there no such indication which can possibly suggest that these items will make a group. So the only way is to generate all combinations. Let me see if I can accomplish it.  Register To Reply

5. ## Re: Sumifs Labeling

Ok, now i understand. Let me know what you find. FYI the original data sheets is like 5k Lines,setup the exact same way.If you are setting up a vba could you include to extraction of all the group data to tab or a new worksheet. New worksheet would be preferred  Register To Reply

6. ## Re: Sumifs Labeling

Also as a FYI that you probably already know in the original data Column1 aka H. Is not there.  Register To Reply

7. ## Re: Sumifs Labeling

What are you trying to group on, and what would some sample answers look like?  Register To Reply

8. ## Re: Sumifs Labeling

Grouping by Operation/Name or Column C. Because the title can change and per the attachment is example. Notice Tab Sheet 2 for the extraction of data that would normally be in a different Workbook as Sheet 1. Is this what you were referring to?

=IF(ROUND(SUMIFS([Amount],[OPERATION/NAME],[@[OPERATION/NAME]]),2)=0,"Zero")  Register To Reply

9. ## Re: Sumifs Labeling

Hello

Finally I think I made it, this is the code- ``Please Login or Register  to view this content.``
It is a quite complex code so I cannot guarantee just by looking at it that it would work fine. Probably you can test it properly and see if it seems to yield correct results.
Speed was of course the main concern so I made sure to minimize processing time as much as I can by minimizing the traffic between the sheet and the macro.

Do test it and let me know how it works.
Example Data1.xlsm

Regards
Sourabh  Register To Reply

10. ## Re: Sumifs Labeling

Continue to test but so far it is working. Question. I notice column H needs to be present having the formula already ran. Is it possible to include that logic in the beginnging of the vba for that formula. I actually insert column H, Apply the formula to all lines with the formula below. If that is to much for the system to handle i can run this into two forms. Or can i run two vba. Can you help me with that as well. Attached is an example of the original Data.

=IF(ROUND(SUMIFS([Amount],[OPERATION/NAME],[@[OPERATION/NAME]]),2)=0,"Zero")  Register To Reply

11. Yes, that is easy.

It's 2:30 am here. Will do that when I wake up Regards
Sourabh  Register To Reply

12. ## Re: Sumifs Labeling

Totally understandable  Register To Reply

13. ## Re: Sumifs Labeling

Will this logic or process work better in Access  Register To Reply

14. ## Re: Sumifs Labeling

Need help adding the below formula in the beginning of this vba and Appling the formula to all lines. Then taking that Grouped data of Zero by [Operation /Name ] and creating a tab for each, labeling the tab by their [operation name] and then run the Full vba that i have attached to each tab that will have a running number that is not used twice in the file so i can have a summary tab of all Grouped number in its separate tab with data.

=IF(ROUND(SUMIFS([Amount],[OPERATION/NAME],[@[OPERATION/NAME]]),2)=0,"Zero")

I apologize this came thru twice. I couldnt find my orginal email.  Register To Reply

15. ## Re: Sumifs Labeling

Sourabh

How are you? Need your help once more with the Example Data1.xlsm spreadsheet. Is there a way to adjust the Macro to Label per Column A. Meaning if the number in Column A is the same, sum all and label and the entire group per A and then go to the next and do the same. I will drop data into this spreadsheet in order by Column A. For this file all that is need is a label by column A that sum to zeros.I have attached and example of what it would look like. Also could have the collumen H be moved to AB of AC for the search as welll.  Register To Reply