Excel: 'Sumifs' ignorando #n/a

Question

Estou tentando somar alguns valores (positivos e negativos) em uma coluna, mas não vai somar porque há valores #n/a. Existe alguma maneira de contornar isso? Por favor, veja o exemplo abaixo:

Cada cidade tem áreas mais baixas com pontuações de renda, crime e desemprego (positivas ou negativas). Meu objetivo é obter as pontuações de renda, crime e desemprego em toda a cidade somando as pontuações das áreas mais baixas. Estou usando 'sumifs' em G2 como em =SUMIFS(C$2:C$10,$A$2:$A$10,$A2) e arrastando-o para I10. Neste exemplo de brinquedo, existem apenas 10 linhas, mas meus dados têm 1 milhão de linhas, então não posso realmente arrastar. Qualquer sugestão sobre isso também seria útil!

Mas, o mais importante, meu problema é que não posso usar 'sumifs' devido aos valores #N/A. Eu quero ignorá-los.

Ou simplesmente substituir todos os valores #N/A por 0 em 1 milhão de linhas também seria uma opção.

ps Eu fiz algumas pesquisas sobre questões semelhantes anteriores, mas elas parecem usar fórmulas 'sumifs' diferentes ...

Answer 1

Usando SUMIFS com <0 AND >0

Você poderia usar isso:

=SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,"<0")+SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,">0")

Evita zero (o que não faz diferença neste caso). Ele também evita células que contêm valores que não são números, como NA, o que geralmente impede a soma (evitá-los resolve seu problema neste caso).

Se bem entendi, você deseja obter a soma dos números positivos e negativos (ou seja, seus valores existentes, não valores absolutos), no entanto, isso está sendo evitado usando sua abordagem existente pela presença dos itens NA.

Se isso não estiver correto, por favor, informe.

USANDO UMA PESQUISA INTERINA, DEPOIS SUMIF COM <0 E >0

Como alternativa, você pode inserir uma coluna de pesquisa, que concatena os valores a serem avaliados e, em seguida, usar SUMIF.

Por exemplo, nova coluna J:

=$A2&C2

(observando o $ para o A, bem como a ausência de $ para o C)

Depois preencha J1 à direita até K1 e L1 Depois em M1:

=SUMIF(J$2:J$10,"<0",C$2:C$10)+SUMIF(J$2:J$10,">0",C$2:C$10)

Em seguida, preencha à direita para N1 e O1

O problema potencial com essa abordagem é que, se você preencher milhões de linhas, a planilha pode ficar significativamente mais lenta.

SUBSTITUINDO NA

Substituir o NA por 0 também funcionará, mas talvez você não queira perder a distinção entre "0" e "não mostrado nos dados de origem".

Por favor, poste sua fórmula existente para os valores de NA se quiser seguir esse caminho. Eles podem ser reformulados em algo em vez de NA que seja significativo e não impeça a soma.

Muitas vezes, é preferível, ao pesquisar dados, capturar a possibilidade de NA e retornar algum outro resultado mais significativo (ou, neste caso, mais amigável à soma).

Answer 2

Talvez a maneira mais fácil seja usar o "<>#N/A"critério como em que irá ignorar NA na coluna de soma. Você pode adicionar critérios adicionais para outras colunas de condições se estiverem interferindo nas verificações de condições numéricas.=SUMIFS(C$2:C$10,$A$2:$A$10,$A2, C$2:C$10, "<>#N/A")

Obrigado:https://www.mrexcel.com/board/threads/sumifs-while-ignoring-n-a.1042338/para a ponta.