Ga naar hoofdinhoud

Hoe zichtbare cellen alleen optellen op basis van criteria in Excel?

In Excel is het misschien gemakkelijk voor ons om cellen op te tellen op basis van criteria met de SOMMEN-functie. Als er echter een bereik van gefilterde gegevens is, om de zichtbare cellen alleen op te tellen met een of meer criteria, zoals hieronder afgebeeld, hoe zou u deze taak in Excel kunnen bereiken?


Som zichtbare cellen alleen op basis van een of meer criteria met hulpkolom

Om de totale waarden van de zichtbare cellen alleen op basis van een of meer criteria te krijgen, kunt u eerst een formulehulpkolom maken en vervolgens de SOMMEN-functie toepassen om het totale resultaat te krijgen.

Om bijvoorbeeld de totale bestelling van het product Hoodie in het gefilterde assortiment te krijgen, gaat u als volgt te werk:

1. Voer de volgende formule in of kopieer deze naar een lege cel naast uw gegevensbereik en sleep vervolgens de vulgreep naar beneden om deze formule naar andere zichtbare cellen te vullen, zie screenshot:

=AGGREGATE(9,5,D4)

2. Nadat u de zichtbare waarden van kolom D hebt gekregen, moet u de onderstaande SUMIFS-functie gebruiken om de totale waarde te krijgen, zie screenshot:

=SUMIFS(E2:E12,A2:A12,A17)
Note: In de bovenstaande formule, E2: E12 is de hulpkolom die u in stap 1 hebt gemaakt en die u wilt optellen; A2: A12 is het criteriabereik, en A17 is de voorwaarde die moet worden gebruikt op het criteriumbereik.

Tips: Als u de zichtbare cellen wilt optellen op basis van meer criteria, zoals om de totale bestelling van het product Hoodie die rood is, te krijgen, past u de onderstaande formule toe:
=SUMIFS(E2:E12,A2:A12,A17,C2:C12,B17)

Als er meer criteria moeten worden toegevoegd, hoeft u zich alleen maar aan te sluiten bij de criteria binnen de SOMMEN-functie als volgt: =SUMIFS(som_bereik, criteria_bereik1, criteria1, [criteria_bereik2, criteria2], [criteria_bereik3, criteria3], ...)


Som zichtbare cellen alleen op basis van criteria met formule

U kunt ook een formule maken op basis van de functies SOMPRODUCT, SUBTOTAAL, OFFSET, RIJ en MIN om zichtbare cellen op te tellen op basis van een of meer criteria in Excel.

Kopieer of voer de onderstaande formule in een lege cel in om het resultaat uit te voeren en druk vervolgens op invoeren toets om het resultaat te krijgen, zie screenshot:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A12,ROW(A2:A12)-MIN(ROW(A2:A12)),,1)),(A2:A12=A17)*(D2:D12))

Note: In de bovenstaande formule, A2:A12=A17 is het criteriabereik en de criteria die u wilt gebruiken, en D2: D12 is het sombereik dat u wilt optellen.
Tips: Om de zichtbare cellen op te tellen op basis van meerdere criteria, hoeft u alleen de criteria als volgt toe te voegen: =SUMPRODUCT(SUBTOTAL(3,OFFSET(reference,ROW(reference)-MIN(ROW(reference)),,1)),(criteria_range1=criteria1)*(criteria_range2=criteria2)*(criteria_range3=criteria3)*(sum_range)).

  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...
kte tabblad 201905
  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
officetab onderkant
Comments (1)
Rated 3.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
What if the one of the criteria is an empty cell? I tried using "= 0" or "" but it does not work.
Rated 3.5 out of 5
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations