Excel: 'Sumifs' ignorando #n/a

Question

Sto provando a sommare alcuni valori (sia positivi che negativi) in una colonna, ma non verrà sommato perché ci sono valori #n/a. C'è un modo per aggirarlo? Si prega di vedere l'esempio qui sotto:

Ogni città ha aree inferiori con punteggi di reddito, criminalità e disoccupazione (positivi o negativi). Il mio obiettivo è ottenere punteggi di reddito, criminalità e disoccupazione in tutta la città sommando i punteggi delle aree inferiori. Sto usando 'sumifs' in G2 come in =SUMIFS(C$2:C$10,$A$2:$A$10,$A2) e poi trascinandolo in I10. In questo esempio di giocattolo, ci sono solo 10 righe, ma i miei dati hanno 1 milione di righe, quindi non posso davvero trascinare. Qualsiasi suggerimento su questo sarebbe anche utile!

Ma, soprattutto, il mio problema è che non posso usare "sumifs" a causa dei valori #N/A. Voglio ignorarli.

Oppure anche la semplice sostituzione di tutti i valori #N/A con 0 in 1 milione di righe sarebbe un'opzione.

ps Ho fatto delle ricerche su precedenti domande simili, ma sembrano usare diverse formule "sumifs" ...

Answer 1

Utilizzo di SUMIFS con <0 AND >0

Potresti usare questo:

=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 lo zero (che in questo caso non fa differenza). Evita anche le celle che contengono valori che non sono numeri, come NA, che spesso impedisce la somma (evitando questi risolve il problema in questo caso).

Se ho capito bene, vuoi ottenere la somma di entrambi i numeri positivi e negativi (cioè i loro valori esistenti, non valori assoluti), tuttavia ciò è attualmente impedito utilizzando il tuo approccio esistente dalla presenza degli elementi NA.

Se questo non è corretto, si prega di avvisare.

UTILIZZANDO UNA RICERCA INTERIM, POI SOMMA.SE CON <0 E >0

In alternativa, puoi inserire una colonna di ricerca, che concatena i valori da valutare, e quindi utilizzare SOMMA.SE.

Ad esempio, nuova colonna J:

=$A2&C2

(notando il $ per la A così come l'assenza di $ per la C)

Quindi riempi J1 a destra fino a K1 e L1 Quindi in M1:

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

Quindi riempi fino a N1 e O1

Il potenziale problema con questo approccio è che se si riempiono milioni di righe, il foglio di calcolo potrebbe rallentare in modo significativo.

SOSTITUZIONE NA

Anche la sostituzione di NA con 0 funzionerà, ma potresti non voler perdere la distinzione tra "0" e "non mostrato nei dati di origine".

Si prega di pubblicare la formula esistente per i valori NA se si desidera seguire questa strada. Possono essere rielaborabili in qualcosa invece di NA che sia significativo e non impedisca la somma.

Spesso è preferibile quando si cercano i dati per intercettare la possibilità di NA e restituire qualche altro risultato più significativo (o in questo caso, più adatto alla somma).

Answer 2

Forse il modo più semplice è utilizzare i "<>#N/A"criteri in cui ignorerà NA nella colonna della somma. È possibile aggiungere criteri aggiuntivi per altre colonne di condizioni se interferiscono con i controlli delle condizioni numeriche.=SUMIFS(C$2:C$10,$A$2:$A$10,$A2, C$2:C$10, "<>#N/A")

Grazie:https://www.mrexcel.com/board/threads/sumifs-while-ignoring-n-a.1042338/per la punta.