How could I sum values using individual filters?

1

Hello community !!

Guys, I made a doc where I’m planning my team’s month of work and I use some indicators to guide me in the best setup. However, I came across a difficulty in generating a sum of values ​​for item 8 and item 9 on picture. Basically the two are a sum of information from the spreadsheet below (named "Planejamento Mensal - Lista), but what I really want to do is a sum dependent on the filter I’m using above. For example, if I am using the “Empresa” filter filled with “Acessórios Vitória”, I would like the sum of all hours related to the company to appear, however, if I select the “Área” filter, the sum is only for the “Acessórios Vitoria” in the area I choose and so on choosing other filters.

In resume, it would be a sum that can interpret the filters individually or jointly. I got it through an extensive “if” formula, but the time for calculating the form was over 4 minutes to open the document. Any idea?

image
image2850×1498 254 KB

2

This is the extensive “IF” formula:

if([CurrentUser1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="" && [Current User 1].[Início]="",[Planejamento Mensal - Lista].[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter([Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="" && [Current User 1].[Início]="" && [Current User 1].Fim="",[Planejamento Mensal - Lista].Filter(Colaborador.Contains([Current User 1].[Responsável])).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Responsável]="" && [Current User 1].[Início]="" && [Current User 1].Fim="",[Planejamento Mensal - Lista].Filter([Área].Contains([Current User 1].[Área])).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="" && [Current User 1].[Início]="" && [Current User 1].Fim="",[Planejamento Mensal - Lista].Filter([Serviço].Contains([Current User 1].[Serviço])).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="" && [Current User 1].[Início]="" && [Current User 1].Fim="",[Planejamento Mensal - Lista].Filter([Cód. Projeto].Contains([Current User 1].Projeto)).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="" && [Current User 1].[Início]="" && [Current User 1].Fim="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa)).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="",[Planejamento Mensal - Lista].Filter(Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter([Área].Contains([Current User 1].[Área]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter([Serviço].Contains([Current User 1].[Serviço]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter([Cód. Projeto].Contains([Current User 1].Projeto) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Serviço]="",[Planejamento Mensal - Lista].Filter([Área].Contains([Current User 1].[Área]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Área]="",[Planejamento Mensal - Lista].Filter([Serviço].Contains([Current User 1].[Serviço]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter([Serviço].Contains([Current User 1].[Serviço]) and [Área].Contains([Current User 1].[Área]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="",[Planejamento Mensal - Lista].Filter([Cód. Projeto].Contains([Current User 1].Projeto) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Área]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Serviço].Contains([Current User 1].[Serviço]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="" && [Current User 1].[Serviço]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Área].Contains([Current User 1].[Área]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].[Serviço]="" && [Current User 1].[Área]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Cód. Projeto].Contains([Current User 1].Projeto) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].Projeto="",[Planejamento Mensal - Lista].Filter([Serviço].Contains([Current User 1].[Serviço]) and [Área].Contains([Current User 1].[Área]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].[Serviço]="",[Planejamento Mensal - Lista].Filter([Cód. Projeto].Contains([Current User 1].Projeto) and [Área].Contains([Current User 1].[Área]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].[Área]="",[Planejamento Mensal - Lista].Filter([Cód. Projeto].Contains([Current User 1].Projeto) and [Serviço].Contains([Current User 1].[Serviço]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter([Cód. Projeto].Contains([Current User 1].Projeto) and [Serviço].Contains([Current User 1].[Serviço]) and [Área].Contains([Current User 1].[Área]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="" && [Current User 1].[Serviço]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Área].Contains([Current User 1].[Área]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="" && [Current User 1].[Área]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Serviço].Contains([Current User 1].[Serviço]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Serviço].Contains([Current User 1].[Serviço]) and [Área].Contains([Current User 1].[Área]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].[Serviço]="" && [Current User 1].[Área]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Cód. Projeto].Contains([Current User 1].Projeto) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].[Serviço]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Cód. Projeto].Contains([Current User 1].Projeto) and [Área].Contains([Current User 1].[Área]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].[Área]="" && [Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Cód. Projeto].Contains([Current User 1].Empresa) and [Serviço].Contains([Current User 1].[Serviço]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Empresa="",[Planejamento Mensal - Lista].Filter([Cód. Projeto].Contains([Current User 1].Projeto) and [Serviço].Contains([Current User 1].[Serviço]) and [Área].Contains([Current User 1].[Área]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].Projeto="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Serviço].Contains([Current User 1].[Serviço]) and [Área].Contains([Current User 1].[Área]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].[Serviço]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Cód. Projeto].Contains([Current User 1].Projeto) and [Área].Contains([Current User 1].[Área]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].[Área]="",[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Cód. Projeto].Contains([Current User 1].Projeto) and [Serviço].Contains([Current User 1].[Serviço]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

if([Current User 1].[Responsável]="",[Planejamento Mensal - Lista].Filter([Cód. Projeto].Contains([Current User 1].Projeto) and [Serviço].Contains([Current User 1].[Serviço]) and [Área].Contains([Current User 1].[Área]) and Empresas.Contains([Current User 1].Empresa) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum(),

[Planejamento Mensal - Lista].Filter(Empresas.Contains([Current User 1].Empresa) and [Cód. Projeto].Contains([Current User 1].Projeto) and [Serviço].Contains([Current User 1].[Serviço]) and [Área].Contains([Current User 1].[Área]) and Colaborador.Contains([Current User 1].[Responsável]) and [Início]>=[Current User 1].[Início] and Fim<=[Current User 1].Fim).[Horas Planejadas].Sum())))))))))))))))))))))))))))))))))))

3

Hey @Douglas_F_Oliveira,

OK i don’t fully understand your setup due to the verbosity, language and the fact its not a working model / example. However I have some ideas for you to try.

1 - You can accomplish a lot with the Switch() and SwitchIf() formulas that will allow you to compress some of your code.

2 - You can combine some of the formulas into either canvas formulas, or using the above which will clean up the code and make it faster. For example you have four formulas that start with if([Current User 1].Empresa="" && [Current User 1].Projeto="" && [Current User 1].[Serviço]="" you could either canvas that or have that be one condition then then branches out.

Overall I feel like you could really trim it down but it hard to see whats happening without an example. Best of luck!

2 Likes
4

Hi Johg,

I’m sorry for the English.

First of all thank you for your time.

I’m quite young at Coda and I’m still learning how to use some formulas.

Can you help me with this link?

5

OK so I’m trying to understand what you’ve got going. Looking at the formulas, I would recommend using indentation & SwitchIf() to clean up the formulas, and nest similar conditions together, which will make it easier to track what is going on and see how you can merge.

The other thing to try is to move some of these formulas back into the User table, which I think is ‘Filtros’ in this case. For each column that exists, you could make another with the resulting array of matching rows, and then your SwitchIf() can be greatly simplified by just bringing in the pre-filtered array. I hope that makes sense.

Good luck!

1 Like