Strings tellen met SUMPRODUCT met bepaalde criteria

[Sanglan]

Hoi,

Ik heb een excelsheet met daarin een heleboek tekst waarin ik bepaalde strings wil tellen. Die fragmenten komen uitverschillende jaren. Ik zou graag een formule willen waarbij ik het aantal keer tel dat een bepaalde string voorkomt waarin ik bepaalde criteria kan stellen.

Ik heb een versimpelde versie van mijn werkblad gemaakt. Ik zou in dit geval willen weten
"Hoe vaak komt de letter A voor in fragmenten uit 1950, 1980 en 2001".

Ik weet al wel hoe ik zo'n formule moet opstellen voor de hele range (B2:B8), daarvoor gebruik ik deze formule:

=SUMPRODUCT(LEN($B$2:$B$8)-LEN(SUBSTITUTE($B$2:$B$8;A11;"")))



Kan iemand me vertellen hoe ik deze formule uitbreid/aanpas naar mijn wens?

Alvast hartelijk bedankt!
Sanne

 

[Timshel]

Helpers hebben er meer aan als je het voorbeeldbestand zelf plaatst in plaats van een plaatje ervan.

 

[AlexCEL]

Volgende keer graag een excel-bestand, daarin zijn wat gemakkelijker formules in te voeren/testen.

B11: =SOMPRODUCT(($A$2:$A$8=B$10)*(LENGTE($B$2:$B$8)-LENGTE(SUBSTITUEREN($B$2:$B$8;$A11;""))))

Voor deze keer heb ik je voorbeeld nog omgezet en de functies daarin ingevuld. Scheelt je ook weer vertalen naar Engels.

 

[Sanglan]

Excuus, geen plaatjes dus Maar super bedankt voor de hulp!

Ik zie dat hij het nu doet met enkele karakters, maar als ik een string zoek dan gaat er het een en ander mis. Is deze formule ook voor strings te gebruiken?
(reden dat ik deze versimpelde versie gebruik is dat het echte bestand vol staat met Chinese taal, dus dat is wat lastig werken voor veel mensen denk ik)

Bijvoorbeeld:
Als ik A of D zoek dan gaat het goed, maar 'AD' of 'DA' geeft onlogische resultaten. Ik kom er eigenlijk niet zo goed uit wat hij nu precies optelt.

 

[Timshel]

=sumproduct(($a$2:$a$8=b$10)*(len($b$2:$b$8)-len(substitute($b$2:$b$8;$a11;""))))/len($a11)

 

[AlexCEL]

De formule telt eigenlijk het verschil in lengte tussen de gehele tekst met zoekstring en de tekst met de zoekstring vervangen door niets. Dit verschil is precies het aantal keer dat de zoekstring voorkomt, mits de zoekstreng lengte 1 heeft... Bij langere zoekstrings moet je nog even delen door de LENGTE van de zoekstring en je bent weer klaar...

Vb:
- ik zoek "AA" in CCAABB. Lengte CCAABB = 6. Vervang AA door niets, lengte CCBB = 4. Verschil is 2. Delen door lengte AA (2) = 1. AA komt 1x voor.
- als ik XXX zoek in XYZXYXXXBB. Lengte is = 10. Na vervangen XXX door niets is lengte 7. Verschil is 3. Delen door lengte XXX (3) = 3. XXX komt 1x voor.

De SOMPRODUCT zorgt ervoor dat dit gebeurt voor elk regel in het zoekbereik, en telt de resultaten op.

 

[Sanglan]

Wat is dit toch een fantatisch forum! Zelfs nog even met uitleg over wat er nu eigenlijk gebeurd.

Heel erg bedankt! Heb de formule inmiddels succesvol in mijn eigen sheet gekregen!