Som met meerdere criteria

[marsclone]

Beste Allen,
Ik kom er even niet uit.
Ik wil graag waarden bij elkaar optellen die aan meerdere criteria moet voldoen, welke in een soort matrix staan.
De formule met de 1e criteria reeks loopt nog goed

{=SOM(SOMMEN.ALS(Data!B:B;Data!B:B;">0";Data!A:A;Voorbeeld!H4:U4))}

Echter als ik de 2e criteria reeks toevoeg, is het resultaat nihil

{=SOM(SOMMEN.ALS(Data!B:B;Data!B:B;">0";Data!A:A;Voorbeeld!H4:U4;Data!C:C;Voorbeeld!H5:U5))}

Dit wordt volgens mij veroorzaakt, doordat in de criteria reeks een waarde staat die niet in de lijst voorkomt. Deze waarde hoeft in één maand niet voor te komen, maar wel weer in de volgende maand. Deze criteria reeks wil ik dus in stand houden.

Kan het zijn, dat ik een andere formule moet toepassen? Bedankt!

 

[jkpieterse]

Bij de SOMMEN.ALS functie wordt altijd "AND" gebruikt tussen de criteria, dus per rij moeten alle criteria een TRUE opleveren.

 

[E v R]

{=SOM(SOMMEN.ALS(Data!B:B;Data!B:B;">0";Data!A:A;Voorbeeld!H4:U4;Data!C:C;transponeren(Voorbeeld!H5:U5)))}

doet 'ie het zo wel?

 

[AlexCEL]

Probeer deze eens (geen matrixfunctie...):

=SOMPRODUCT(N(ISGETAL(VERGELIJKEN(Data!$A$2:$A$203;$G$4:$P$4;0)))*N(ISGETAL(VERGELIJKEN(Data!$C$2:$C$203;$G$5:$J$5;0)))*N(Data!$B$2:$B$203>0)*(Data!$B$2:$B$203))

Uitkomst = € 153.040,96

PS ik heb aangenomen dat de criteria in kolom G t/m P gebruikt moeten worden en de rekeningen in kolom G t/m H. In je voorbeeld noem je een bereik dat 1 kolom opgeschoven is... maar mocht dat correct zijn, dan valt dat zo aan te passen hierboven.

 

[marsclone]

Beste Eric en Alex,

Beide functies geven de gewenste uitkomst! Bedankt hiervoor!

De functie 'transponeren' had ik hier niet verwacht. Dan ben ik eigenlijk wel benieuwd wat deze hier precies doet.

De functie met SOMPRODUCT(ISGETAL(VERGELIJKEN) heb ik ook mee lopen stoeien, maar gaf geen waarde.
Wat doet de toevoeging 'N' hierin?

Nogmaals dank!

 

[E v R]

@ Alex, jouw formule werkt ook zonder de N funktie, scheel t weer 3 N-tjes en 4 haakjes
overigens zelfde uitkomst als met transponeren uit post#3 :thumb:

 

[marsclone]

Tevens de vraag?

Welke oplossing is eigenlijk de meest betrouwbare, flexibele, ....

Of is er geen verschil?

 

[E v R]

De functie 'transponeren' had ik hier niet verwacht. Dan ben ik eigenlijk wel benieuwd wat deze hier precies doet.

Zoals JKP in post#2 al aangaf gebruikt sommen.als AND, maar mbv die transpose maak je er een OR van, (in jouw formule uit post #1 wordt er enkel gekeken naar cel H5)

++In dit geval zou ik gaan voor Alexcel z'n oplossing, die transpose methode is lastiger te doorgronden en ik denk dat ie ook minder efficient is

 

[AlexCEL]

... jouw formule werkt ook zonder de N funktie, scheel t weer 3 N-tjes en 4 haakjes

Dat is zo ja... de N-functie zet het mooi om in 0 en 1, maar is overbodig want rechtstreeks WAAR en ONWAAR vermenigvuldigen kan ook.

Het voordeel van de SOMPRODUCT functie is dat het werkt als een matrixfunctie, maar dat je het 'm niet met Control+Shift+Enter hoeft in te geven. Minder kans op fouten denk ik dan.

 

[marsclone]

Met name als ik het document door wil sturen, is het veiliger, dat er geen Ctrl+Sh+E ingegeven hoeft te worden.

Laatste vraagje?

Als ik nu in de som, juist de 2e criteria reeks wil uitsluiten. Volgens mij moet ik dan werken met '<>', maar waar plaats ik deze dan in de formule?

=SOMPRODUCT(ISGETAL(VERGELIJKEN(Data!A2:A300;Voorbeeld!H4:U4;0))*(ISGETAL(VERGELIJKEN(Data!C2:C300;Voorbeeld!H5:U5;0))*Data!B2:B300>0)*(Data!B2:B300))

 

[AlexCEL]

Dan gebruik je bijvoorbeeld de functie NIET:

=SOMPRODUCT(ISGETAL(VERGELIJKEN(Data!$A$2:$A$203;$G$4:$P$4;0))*[COLOR="#FF0000"]NIET[/COLOR](ISGETAL(VERGELIJKEN(Data!$C$2:$C$203;$G$5:$J$5;0)))*(Data!$B$2:$B$203>0)*(Data!$B$2:$B$203))

 

[marsclone]

Het vreemde is dan, dat de formule geen rekening houdt met alleen de waarden >0?

 

[jkpieterse]

Als je vraagt wat ik van de oplossingen vind: Een één-cel formule met een dergelijke complexiteit ben ik geen voorstander van. Ik zou ervoor pleiten de berekening op te splitsen door hulpkolommen toe te voegen aan de gegevens die bepalen of een rij wel of niet mee mag tellen. Dan kan je per rij ook trouble-shooten of je berekening goed gaat. En eindig je met een simpele SOM.ALS formule.

Wat je ook doet, documenteer de werking van de geboden formules goed, want over een half jaar wordt het lastig terugredeneren wat je formule ook alweer doet

 

[marsclone]

Het vreemde is dan, dat de formule geen rekening houdt met alleen de waarden >0?

Moest nog een haakje voor 'NIET'

 

[AlexCEL]

Volgens mij NIET.. formule in #11 werkt prima bij mij. Uitkomst is 188903,71.