Excel formule combineren

[Arent Schaap]

Mijn vraag
Ik wil deze 2 formules combineren
=ALS(K30="x";0;1)
=SOM('1:161'!M27)

Met de eerste geef ik aan dat dat een factuur is afgedrukt ( vandaar de x)
Nu wil ik een overzicht creëren over de hele werkmap. En kunnen zien dat als ergens de cel K30 geen x bevat dat er een factuur niet verstuurd is.
Met de 2e formule tel ik een willekeurige cel op over een aantal werkbladen.
Kan ik dan ook het bereik instellen vanaf werkblad 1 tot einde. Zodat ik bij het maken van een nieuw werkblad de waardes niet hoef te wijzigen?

Relevante software en hardware die ik gebruik
EXCEL

Wat ik al gevonden of geprobeerd heb
...

[Freezerator]

Daar is ook een formule voor. SUMIF in het engels.

In het nederlands SOM.ALS https://support.office.co...5c-4483-a712-1697a653039b

[Arent Schaap]

Dan wil ik niet heel vervelend zijn, kan jij deze voor mij schrijven met de waarde hierboven?
En is het mogelijk om dus het bereik tot elke keer het einde te laten lopen?

[pacificocean]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 15:58:
Dan wil ik niet heel vervelend zijn, kan jij deze voor mij schrijven met de waarde hierboven?
En is het mogelijk om dus het bereik tot elke keer het einde te laten lopen?

Volgens mij is dit gewoon een verzoek om het werk te doen waar jij voor betaald wordt.

[Flimovic]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 15:58:
Dan wil ik niet heel vervelend zijn, kan jij deze voor mij schrijven met de waarde hierboven?
En is het mogelijk om dus het bereik tot elke keer het einde te laten lopen?

Een beetje eigen inzet is wel vereist. Probeer eens met de microsoft help functie (die echt prima is in excel) de formules te combineren.

[Verwijderd]

Een SUMIF (of COUNTIF, wat hij eigelijk lijkt te willen) over meerdere tabbladen, gaat je niet lukken zonder dan INDIRECT gebruik te maken.

Zowel SUMIF als COUNTIF werken namelijk alleen op een range die op een enkele sheet staat.

edit:
https://answers.microsoft...1e-4b13-a8ad-89047273f711

Voor als je toch een enkele cell wil gebruiken. Het wordt er niet leesbaarder op.

[ Voor 35% gewijzigd door Verwijderd op 01-08-2018 16:13 ]

[Arent Schaap]

Heb het net geprobeerd, maar het lukt niet. Echter is dit een vrijetijds- projectje.
Kom er gewoon niet uit, niks uit gemakzucht maar ik loop helemaal vast als ik die =som.als wil toepassen

[Verwijderd]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 16:12:
Heb het net geprobeerd, maar het lukt niet. Echter is dit een vrijetijds- projectje.
Kom er gewoon niet uit, niks uit gemakzucht maar ik loop helemaal vast als ik die =som.als wil toepassen

Dat begrijp ik, zie mijn vorige post. SOM.ALS kan dit zonder trucjes niet.

[Flimovic]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 16:12:
Heb het net geprobeerd, maar het lukt niet. Echter is dit een vrijetijds- projectje.
Kom er gewoon niet uit, niks uit gemakzucht maar ik loop helemaal vast als ik die =som.als wil toepassen

Laat dan zien welke formule hebt gemaakt en/of wat er precies niet lukt, dan kunnen wij proberen aan te geven waar je de mist in gaat.

En zoals ik net al zei, als je de helpfunctie van excel er bij pakt, moet je een flink eind komen. Bekijk niet alleen het SOM.ALS stuk in de help, maar ook de ALS functie en de SOM functie.

[Verwijderd]

Flimovic schreef op woensdag 1 augustus 2018 @ 16:14:
[...]


Laat dan zien welke formule hebt gemaakt en/of wat er precies niet lukt, dan kunnen wij proberen aan te geven waar je de mist in gaat.

En zoals ik net al zei, als je de helpfunctie van excel er bij pakt, moet je een flink eind komen. Bekijk niet alleen het SOM.ALS stuk in de help, maar ook de ALS functie en de SOM functie.

Ik denk dat jij het probleem onderschat, met de standaard help van som.als, som en al, kom je er echt niet.

Het antwoord wat ik gelinkt heb is een array functie welke je met somproduct sommeert, omdat somproduct standaard zonder ctrl-shift-enter met arrays kan rekenen. (De INDIRECT levert als resultaat een array!)

Ik weet niet hoeveel ervaring TS met Excel heeft, maar Array functies zijn niet echt beginner vriendelijk. Ik ken zelfs genoeg mensen die liever een VBA macrotje schrijven dan een array functie gebruiken.

[ Voor 11% gewijzigd door Verwijderd op 01-08-2018 16:21 ]

[Arent Schaap]

Verwijderd schreef op woensdag 1 augustus 2018 @ 16:19:
[...]

Ik denk dat jij het probleem onderschat, met de standaard help van som.als, som en al, kom je er echt niet.

Het antwoord wat ik gelinkt heb is een array functie welke je met somproduct sommeert, omdat somproduct standaard zonder ctrl-shift-enter met arrays kan rekenen. (De INDIRECT levert als resultaat een array!)

Ik weet niet hoeveel ervaring TS met Excel heeft, maar Array functies zijn niet echt beginner vriendelijk. Ik ken zelfs genoeg mensen die liever een VBA macrotje schrijven dan een array functie gebruiken.

Nu ben ik echt de draad kwijt haha

[Arent Schaap]

Flimovic schreef op woensdag 1 augustus 2018 @ 16:14:
[...]


Laat dan zien welke formule hebt gemaakt en/of wat er precies niet lukt, dan kunnen wij proberen aan te geven waar je de mist in gaat.

En zoals ik net al zei, als je de helpfunctie van excel er bij pakt, moet je een flink eind komen. Bekijk niet alleen het SOM.ALS stuk in de help, maar ook de ALS functie en de SOM functie.

Heb deze geprobeerd =SOM.ALS(('1:161'!M27)(K30="x";0;1))

[Niet_Jan_Jaap]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 16:42:
[...]


Nu ben ik echt de draad kwijt haha

Arent Schaap schreef op woensdag 1 augustus 2018 @ 16:43:
[...]


Heb deze geprobeerd =SOM.ALS(('1:161'!M27)(K30="x";0;1))

ik denk dat je met SOM.ALS dit niet op gaat lossen, zou je iets duidelijker willen omschrijven (evt. met voorbeelddata) wat je wilt bereiken? dan kan ik er even naar kijken.


edit:

als je de 2 formules gewoon wilt combineren (dus de range bij elkaar optellen als er aan een bepaalde waarde wordt voldaan) kan je dit gewoon doen:

=ALS(K30="x";SOM('1:161'!M27);1)

dan zeg je dus: als K30 = x, laat dan de som van '1:161'!M27 zien, anders laat '1' zien

[ Voor 94% gewijzigd door Niet_Jan_Jaap op 01-08-2018 17:00 ]

[Arent Schaap]

Niet_Jan_Jaap schreef op woensdag 1 augustus 2018 @ 16:46:
[...]


[...]


ik denk dat je met SOM.ALS dit niet op gaat lossen, zou je iets duidelijker willen omschrijven (evt. met voorbeelddata) wat je wilt bereiken? dan kan ik er even naar kijken.

In het excel bestand staat op elke werkblad:
K29: Afgedrukt
K30: x

Nu wil ik in 1x kunnen zien of er nog werkbladen(facturen niet afgedrukt zijn.

Dus de uitkomst van de formule moet over alle werkbladen 0 zijn. Staat de uitkomst op 6 dan weet je dat er 6 facturen nog niet verstuurd zijn. Hoop dat ik je zo voldoende informatie heb gegeven, samen met het bovenstaande

[Arent Schaap]

K30: x geeft aan dat hij afgedrukt is. Dus als er een x staat kan de waarde op 0 komen want deze is verstuurd. Staat er nog geen "x" dan moet deze nog verstuurd worden

[Niet_Jan_Jaap]

Ok, tis al iets duidelijker, maar nog niet helemaal.

Zijn de werkbladen statisch of verandert het aantal werkbladen (komen er werkbladen bij / af, of is het altijd hetzelfde aantal werkbladen?)

Als de werkbladen hetzelfde blijven is het niet heel ingewikkeld, dan kan je een simpele COUNTIF toepassen. Mocht het aantal werkbladen wijzigen, dan is het niet simpel op te lossen, dan zou je dit moeten doen:
https://www.computing.net...ple-worksheets/17296.html

[ Voor 5% gewijzigd door Niet_Jan_Jaap op 01-08-2018 17:01 ]

[Arent Schaap]

Niet_Jan_Jaap schreef op woensdag 1 augustus 2018 @ 17:00:
Ok, tis al iets duidelijker, maar nog niet helemaal.

Zijn de werkbladen statisch of verandert het aantal werkbladen (komen er werkbladen bij / af, of is het altijd hetzelfde aantal werkbladen?)

Als de werkbladen hetzelfde blijven is het niet heel ingewikkeld, dan kan je een simpele COUNTIF toepassen. Mocht het aantal werkbladen wijzigen, dan is het niet simpel op te lossen, dan zou je dit moeten doen:
https://www.computing.net...ple-worksheets/17296.html

De werkbladen veranderen inderdaad.

[Niet_Jan_Jaap]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 17:17:
[...]


De werkbladen veranderen inderdaad.

ervan uitgaande dat de werkbladen wel dezelfde naam behouden kan je de uitdaging aangaan en dit proberen (uit mijn eerdere link):

First put your Sheet Names in cells AA1 through AA100,
I've used only 9 here as an example, and you can use any column you like.

So your data looks like:

AA
1) Sheet2
2) Sheet3
3) Sheet4
4) Sheet5
5) Sheet6
6) Sheet7
7) Sheet8
Sheet9
9) Sheet10
Now on Sheet1, cell A1, just enter the formula,

=SUMPRODUCT(COUNTIF(INDIRECT(""&AA1:AA9&"!K30"),"x"))

wel even vertalen naar de Nederlandse Excel termen, maar daar ben ik zelf geen held in

[Verwijderd]

Als de werkbladen vernaderen, dan ontkom je niet aan twee formules en is wat je nu hebt het beste wat je kan doen, zonder naar macros te grijpen.

Aan de andere kant, met een macro kan je mooi een comma seperated lijstje in je uitvoercel laten zetten met de tabblad namen van de facturen die niet verzonden zijn. Dat lijkt me bij een grote hoeveelheid praktischer dan constateren dat er onverzonden facturen zijn en dan alle facturen langs lopen.

Je zou zelfs een macro kunnen schrijven die ieder tabblad naloopt op een x en als deze er niet staat de tabkleur rood maakt en als hij er wel staat de tab kleur groen. Als je dan ook nog controleert of de cel erboven de tekst "verzonden" oid bevat voor je dat doet slaat hij automatisch tabbladen waarin geen factuur staat over.

[Arent Schaap]

Niet_Jan_Jaap schreef op woensdag 1 augustus 2018 @ 17:22:
[...]


ervan uitgaande dat de werkbladen wel dezelfde naam behouden kan je de uitdaging aangaan en dit proberen (uit mijn eerdere link):

First put your Sheet Names in cells AA1 through AA100,
I've used only 9 here as an example, and you can use any column you like.

So your data looks like:

AA
1) Sheet2
2) Sheet3
3) Sheet4
4) Sheet5
5) Sheet6
6) Sheet7
7) Sheet8
Sheet9
9) Sheet10
Now on Sheet1, cell A1, just enter the formula,

=SUMPRODUCT(COUNTIF(INDIRECT(""&AA1:AA9&"!K30"),"x"))

wel even vertalen naar de Nederlandse Excel termen, maar daar ben ik zelf geen held in

Allereerst bedankt voor de moeite!
heb hem vertaald maar hij werkt niet
=SOMPRODUCT(AANTAL.ALS(INDIRECT(""&AA1:AA9&"!K30"),"x"))

mijn werkbladen heten 1,2,3,4, enz. gaat het hiermee fout?

[Verwijderd]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 17:29:
[...]


Allereerst bedankt voor de moeite!
heb hem vertaald maar hij werkt niet
=SOMPRODUCT(AANTAL.ALS(INDIRECT(""&AA1:AA9&"!K30"),"x"))

mijn werkbladen heten 1,2,3,4, enz. gaat het hiermee fout?

Durf ik niet direct te zeggen. Maar probeer ens de properties van AA1:AA9 te wijzigen naar 'tekst' en kijk of dat een verschil maakt.

[Niet_Jan_Jaap]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 17:29:
[...]


Allereerst bedankt voor de moeite!
heb hem vertaald maar hij werkt niet
=SOMPRODUCT(AANTAL.ALS(INDIRECT(""&AA1:AA9&"!K30"),"x"))

mijn werkbladen heten 1,2,3,4, enz. gaat het hiermee fout?

je moet wel op de sheet waar je de formule hebt staan in de kolom AA dan 1,2,3,4 zetten, heb je dat gedaan?
dus cel AA1 > 1
AA2 > 2
etc.

btw: ik heb em getest en bij mij werkt ie, dus moet je em ook wel werkend kunnen krijgen

edit 2: waar in de formule AA9 staat moet je aanpassen naar het aantal tabbladen dat je hebt, dus bij 50 tabbladen wordt het =SOMPRODUCT(AANTAL.ALS(INDIRECT(""&AA1:AA51&"!K30"),"x"))

[ Voor 22% gewijzigd door Niet_Jan_Jaap op 01-08-2018 17:34 ]

[Arent Schaap]

Niet_Jan_Jaap schreef op woensdag 1 augustus 2018 @ 17:32:
[...]


je moet wel op de sheet waar je de formule hebt staan in de kolom AA dan 1,2,3,4 zetten, heb je dat gedaan?
dus cel AA1 > 1
AA2 > 2
etc.

btw: ik heb em getest en bij mij werkt ie, dus moet je em ook wel werkend kunnen krijgen

edit 2: waar in de formule AA9 staat moet je aanpassen naar het aantal tabbladen dat je hebt, dus bij 50 tabbladen wordt het =SOMPRODUCT(AANTAL.ALS(INDIRECT(""&AA1:AA51&"!K30"),"x"))

sorry ik volg je even niet

[Niet_Jan_Jaap]

Arent Schaap schreef op woensdag 1 augustus 2018 @ 17:35:
[...]


sorry ik volg je even niet

ok, ik maak het wat simpeler.

ervan uitgaande dat je 10 tabbladen hebt:

vul in kolom A (eerste kolom van het werkblad) in de cel A1 (helemaal linksboven) '1' in. In cel A2 (de cel eronder) vul je '2' in. zo ga je verder tot je bij '10' ben in cel A10.

vul dan in een willekeurige cel (behalve A1 - A10, want die zijn al bezet) de volgende formule in:
=SOMPRODUCT(AANTAL.ALS(INDIRECT(""&A1:A10&"!K30");"x"))


edit:
een plaatje zegt meer dan 1000 woorden:
https://imgur.com/a/tG8mOfs

in dit plaatje zijn op 3 werkbladen een 'x' ingevuld in cel K30

[ Voor 11% gewijzigd door Niet_Jan_Jaap op 01-08-2018 17:47 ]

[Arent Schaap]

Hallo Niet_Jan_Jaap,

Ik heb het nog even geprobeerd maar het werk niet bij mij. Wat is het nut van het invullen van de cijfers in de cellen a1/a10?

Hij moet van meer dan 200 werkbladen de cel K30 beoordelen, als hier een "X" staat wil ik graag een waarde zien in het eerste werkblad. Dus als 10 werkbladen geen "X" bevat in K30 wil ik graag het getal 10 zien in mijn eerste werkblad. Dan weet ik dus dat er 10 facturen niet afgedrukt zijn. Dit getal moet immers altijd 0 zijn, maar leek me handig om dit zo even te kunnen controleren.

Helaas gaat dit dus niet zo makkelijk haha

[F_J_K]

Som.als werkt AFAIK niet over worksheets heen. Maak er ipv. een x, een 1 of 0 van en doe =SOM(Blad1:Blad4!A1)
Werkt hier in een testje prima.

Offtopic:

Arent Schaap schreef op woensdag 1 augustus 2018 @ 15:58:
Dan wil ik niet heel vervelend zijn, kan jij deze voor mij schrijven met de waarde hierboven?
En is het mogelijk om dus het bereik tot elke keer het einde te laten lopen?

offtopic:
Nee het is hier nooit de bedoeling 'voor te kauwen'. Ook niet bij hobbyprojectjes. Ajb altijd eerst zelf een poging wagen en dan bij vastlopen de eigen formules beschrijven o.i.d. Zoals je later inderdaad ook doet.

Edit: ipv som.als had aantal.als meer voor de hand gelegen, maar ook die doet het niet over worksheets heen.

[ Voor 7% gewijzigd door F_J_K op 02-08-2018 09:46 ]

[Arent Schaap]

Zo had ik het nog niet eens bekeken, als ik nu nieuwe werkbladen maak. Pakt hij deze niet, is hier een oplossing voor of moet ik dit handmatig doen?

Bedankt ben al weer een stuk verder!

F_J_K schreef op donderdag 2 augustus 2018 @ 09:43:
Som.als werkt AFAIK niet over worksheets heen. Maak er ipv. een x, een 1 of 0 van en doe =SOM(Blad1:Blad4!A1)
Werkt hier in een testje prima.

Offtopic:

[...]

offtopic:
Nee het is hier nooit de bedoeling 'voor te kauwen'. Ook niet bij hobbyprojectjes. Ajb altijd eerst zelf een poging wagen en dan bij vastlopen de eigen formules beschrijven o.i.d. Zoals je later inderdaad ook doet.

Edit: ipv som.als had aantal.als meer voor de hand gelegen, maar ook die doet het niet over worksheets heen.

[F_J_K]

Probeer het en je weet het

(Hangt er van af waar je toevoegt).

[Niet_Jan_Jaap]

Arent Schaap schreef op donderdag 2 augustus 2018 @ 09:34:
Hallo Niet_Jan_Jaap,

Ik heb het nog even geprobeerd maar het werk niet bij mij. Wat is het nut van het invullen van de cijfers in de cellen a1/a10?

Hij moet van meer dan 200 werkbladen de cel K30 beoordelen, als hier een "X" staat wil ik graag een waarde zien in het eerste werkblad. Dus als 10 werkbladen geen "X" bevat in K30 wil ik graag het getal 10 zien in mijn eerste werkblad. Dan weet ik dus dat er 10 facturen niet afgedrukt zijn. Dit getal moet immers altijd 0 zijn, maar leek me handig om dit zo even te kunnen controleren.

Helaas gaat dit dus niet zo makkelijk haha

wat je daar vraagt zou precies zo moeten werken als ik beschreef. Er wordt gebruik gemaakt van indirecte referenties, maar ik denk niet dat het nut heeft om dit uit te leggen. Als je dit voor PRECIES 200 tabbladen wilt doen moet je cel a1 - a200 vullen met 1 - 200 zoals ik eerder beschreef. Dit kan je ook op een andere plek in je werkblad doen, maar dan moet je ook de formule aanpassen. In de cel waar je het aantal 'x' over alle tabbladen wil zien moet je de formule plakken. Als je kolom A nodig hebt voor iets anders kan je natuurlijk ook een andere plek kiezen, dan moet je in de formule het stukje A1:A200 aanpassen naar de locatie waar je de cijfers zet.

=SOMPRODUCT(AANTAL.ALS(INDIRECT(""&A1:A200&"!K30"),"x"))

Ik weet alleen niet of dit een handige oplossing voor jou is als het aantal tabbladen continue verandert, omdat je dan de formule iedere keer aan zou moeten passen naar het aantal tabbladen dat je hebt. Dit kan je ook evt. weer met een formule oplossen, maar voordat je dat daar aan gaat is het denk ik handig dat de formule eerst werkt bij jou

Arent Schaap schreef op donderdag 2 augustus 2018 @ 10:33:
Zo had ik het nog niet eens bekeken, als ik nu nieuwe werkbladen maak. Pakt hij deze niet, is hier een oplossing voor of moet ik dit handmatig doen?

Bedankt ben al weer een stuk verder!


[...]

als je dit met een dynamisch aantal tabbladen wilt doen, probeer dan het aantal tabbladen door excel te laten bereken, is vrij makkelijk met de onderstaande link. Als je dat werkend hebt kan je die waarde in de formule op laten nemen, dan doet ie de formule op basis van het echte aantal tabbladen (dynamisch).


link voor aantal tabbladen tellen

[ Voor 14% gewijzigd door Niet_Jan_Jaap op 02-08-2018 13:48 ]