Critérios ITSUMIF para células que não correspondem a uma condição

Question

Eu tenho uma planilha do Excel que se parece com o seguinte:

# |A           |B
-----------------------------
1 |Expenses    |Category
2 |$5          |Food
3 |$10         |Entertainment
4 |$10         |Haircut

O que eu quero é criar uma seção que lista a soma de cada categoria. Algo que é organizado assim:

# |H            |I
-----------------------------
1 |Category     |Total Expenses
2 |Food         |
3 |Entertainment|
4 |Other        |

Resumir as categorias de Food and Enterainment foi muito fácil. Usei a função SUMIF para verificar o intervalo da coluna "Categoria" B em relação a uma célula na coluna "Categoria" H, que adicionava os valores se eles correspondessem. Por exemplo, "I2" tinha uma fórmula assim:

=SUMIF(B$2:B$999, H2, A$2:A$99)

No entanto, estou com dificuldades para configurar a categoria "Outros". O que eu quero é que ele exiba quaisquer valores que tenham uma categoria que não apareça na coluna H (por exemplo, "Corte de cabelo"). Eu tentei fazer o seguinte:

=SUMIF(B$2:B$999, NOT(OR(H2,H3)), A$2:A$99)

Mas isso não parece capturar nenhuma célula. Por fim, quero que a seção de critérios do SUMIF capture as células que não contêm os valores encontrados em um intervalo de outras células.

EDIT: Isto é para uma planilha que é para um cliente que não é muito saavy computador, portanto, qualquer solução deve idealmente ser um que coloca muito pouco fardo em um usuário final (por exemplo, não exige que eles entendam que as macros são OK). Além disso, a planilha completa inclui uma coluna de data, portanto, a classificação por categoria não funcionará.

    

Answer 1

SUMIFS permite especificar mais de uma condição (no Excel 2007 ou posterior) para que você possa usar essa fórmula

=SUMIFS(A:A,B:B,"<>"&H2,B:B,"<>"&H3)

Isso adicionará a coluna A, onde o valor correspondente da coluna B não é igual a H2 AND não igual a H3

Se você tiver uma lista mais longa de itens a serem excluídos dessa maneira, criará uma fórmula longa. Assim, outra maneira de excluir um intervalo seria usar SUMPRODUCT como esta

=SUMPRODUCT(ISNA(MATCH(B2:B100,H2:H5,0))+0,A2:A100)

Isso somará A2: A100 quando o valor correspondente da coluna B não corresponder a nenhum dos valores em H2: H5

    

Answer 2

O que você precisa é de Pivot Table .

Selecione uma das células da sua tabela, digamos A1 . Crie uma tabela dinâmica clicando em: Inserir > Tabela Dinâmica. Clique ok.

Neste ponto, você terá um Pivot Table criado em uma nova planilha. Para calcular o que você deseja, arraste e solte o Category da lista arquivada, na caixa à direita da tabela, para Row Labels , arraste e solte Expenses para a ∑ Values .

EDITAR

Como você fez em sua pergunta, o Pivot Table não precisa ser criado em uma folha separada. Em vez de clicar em OK quando a Tabela Dinâmica solicitar uma caixa de diálogo, você poderá especificar onde deseja criar sua Tabela Dinâmica.

Além disso, lembre-se de atualizar a tabela sempre que o conteúdo for modificado, clicando com o botão direito do mouse na Tabela Dinâmica e clicando em Atualizar.

EDIT 2

Me desculpe, eu completei a leitura da sua pergunta, você conseguiu calcular a soma das Categorias, então você não precisa de uma Tabela Dinâmica. O que você precisa é somar as outras categorias, minha sugestão:

Em vez de tentar somar todas as categorias que não foram somadas, por que você não soma o total de A1: A999 e subtrai todos os valores somados na coluna I?

Suponhamos que o campo Other esteja em H4:

=SUM(A$1:A$999) - SUM(I$2:I$3)

    

Answer 3

Uma maneira simples de fazer isso é classificar os dados por categoria e usar SUBTOTAL. Você pode usar a função SUBTOTAL repetidamente ou (a maneira mais fácil) destacar suas colunas A e B classificadas e usar o subtotal da estrutura de tópicos na Data Ribbon (esqueci de que menu está em versões anteriores do Excel). Isso irá inserir automaticamente subtotais sempre que a categoria for alterada, incluir um total geral e configurar o processamento de tópicos para que você possa recolher ou expandir os subtotais.

O subtotal é muito útil para esse tipo de coisa. Ele suporta várias camadas de totais, para que você possa ter subtotais separados por departamento, por categoria no departamento, etc.

    

Answer 4

Que tal configurar I4 para =SUM(A$2:A$99) - SUM(I$2:I3) - todos os dados da coluna B menos os (sub) totais que você já acumulou na coluna I . Se você precisar adicionar (e / ou excluir) categorias com o mínimo de preocupação e preocupação, consulte

=SUM(A$2:A$99) - SUM(OFFSET(I$2,0,0,ROW()-2,1))

em que a função ROW() descobre em qual linha está (ou seja, qual linha é a linha "Outro"). Subtraia 2 para a linha 1 e a linha atual; por exemplo, se você tiver oito categorias somadas, em Células H2:H9 , Other estará em H10 e você deseja somar I2:I9 , que é oito (10-2) linhas.