somproduct /#WAARDE!

[Egbert12345]

Beste forummers,

Ikgebruik SOMPRODUCT om de som te bepalen van een kolom waarbij er aan een aantal variabelen moet worden voldaan. Als in die kolom nu een lege cel staat, dan krijg ik de uitkomst #WAARDE!. Is dat op te lossen zonder een getal in die cel in te vullen?

gr.Egbert

 

[spaarie]

Excel 2007 of hoger

=ALS.FOUT(formule;"")

Excel 2003 of lager

=ALS(ISFOUT(formule);"";formule)

 

[Egbert12345]

Dank spaarie. Deze formule ken ik, maar ik wilde eigenlijk dat de ontbrekende cel werd genegeerd. Ik los dat nu anders op (maak een macro die de lege cellen opspoort).

In het kader hiervan heb ik een ander probleem. Zie eerste formule, tweede variabele ('[database VWG.xlsm]database'!$CF$2:$CF$5000).
Deze kolom muteert zo nu en dan en daarom heb ik in cel Y9 de letters "CF" staan. Die wijzigt bijvoorbeeld in "CG". In de tweede formule heb ik INDIRECT willen inbouwen, zie tweede formule, maar ik doe iets verkeerd. Krijg de uitkomst #VERW!
Kan jij zo zien wat ik niet goed doe?

=SOMPRODUCT(('[database VWG.xlsm]database'!$CF$2:$CF$5000>0)*('[database VWG.xlsm]database'!$CF$2:$CF$5000)*('[database VWG.xlsm]database'!$N$2:$N$5000=AC12)*('[database VWG.xlsm]database'!$V$2:$V$5000>$Z$3)*('[database VWG.xlsm]database'!$V$2:$V$5000<$Z$4))

=SOMPRODUCT((INDIRECT('[database VWG.xlsm]database'!$CF$2:$CF$5000>0)*("'[database VWG.xlsm]database'!"&Y9&"2:$CF$5000")*('[database VWG.xlsm]database'!$N$2:$N$5000=AC12)*('[database VWG.xlsm]database'!$V$2:$V$5000>$Z$3)*('[database VWG.xlsm]database'!$V$2:$V$5000<$Z$4)))

 

[spaarie]

Je plaatst de INDIRECT formule verkeerd:

=SOMPRODUCT(('[database VWG.xlsm]database'!$CF$2:$CF$5000>0)*(INDIRECT("'[database VWG.xlsm]database'!"&Y9&"2:$CF$5000"))*('[database VWG.xlsm]database'!$N$2:$N$5000=AC12)*('[database VWG.xlsm]database'!$V$2:$V$5000>$Z$3)*('[database VWG.xlsm]database'!$V$2:$V$5000<$Z$4))

Maar waarom maak je geen gebruik van een dynamische naambereik?

Wellicht is het handig om een voorbeeldje te plaatsen, want de formule kan je misschien ook wel veranderen voor een andere aangezien de SOMPRODUCT veel rekenkracht kost. Helemaal over zoveel cellen...

 

[Egbert12345]

Ah, wat dom van mij. Ik had wel nagedacht over een voorbeeldbestand, maar dat was wat lastig.

Dynamisch naambereik? Die ken ik nog niet. Is dat eenvoudig uit te leggen, want anders zal ik de boeken er maar eens op naslaan.

m.vr.gr. Egbert

 

[Egbert12345]

Nog een vraag. Als ik deze regel nu kopieer, dan wijzigt de celverwijzing Y9 automatisch in Y10. Kan ik dat voorkomen?

 

[spaarie]

Een voorbeeldje:
In het lint ga je naar 'Formules' > 'Namen beheren' > 'Nieuw' > Geef een naam voor het bereik in > Kopieer en plak bij 'verwijst naar:'

=VERSCHUIVING([database VWG.xlsm]database!$N$1;1;0;AANTAL.ALS([database VWG.xlsm]database!$N:$N;"<>")-1;1)

Vervolgens kan je dan de naam gebruiken in de formule en is dynamisch

=SOMPRODUCT(('[database VWG.xlsm]database'!$CF$2:$CF$5000>0)*(INDIRECT("'[database VWG.xlsm]database'!"&Y9&"2:$CF$5000"))*([U][B][COLOR="#FF0000"]NAAMBEREIK[/COLOR][/B][/U]=AC12)*('[database VWG.xlsm]database'!$V$2:$V$5000>$Z$3)*('[database VWG.xlsm]database'!$V$2:$V$5000<$Z$4))

 

[spaarie]

Nog een vraag. Als ik deze regel nu kopieer, dan wijzigt de celverwijzing Y9 automatisch in Y10. Kan ik dat voorkomen?

Serieus? Kijk eens naar de bereiken in je formule.. wat staat daar voor teken tussen? Dat heet absoluut maken...

 

[Egbert12345]

Opgelost. Ik heb aan Y9 $-tekens toegevoegd. Bedankt voor de assistentie en ik ga mij verdiepen in : verschuiving.
m.vr.gr. Egbert

 

[Egbert12345]

Hij gaat helemaal lekker, maar ik loop nog tegen éen ding aan. Ik ben echt aan het puzzelen, maar kom er niet uit. Neem het voorbeeld met de Y9 indirect. Nu wil ik >0 toevoegen. Niet persé in dit deel, maar het gaat mij om het principe (wil ik straks ook doen met =AC12 (derde reeks). Hoe plaats ik die?

=SOMPRODUCT(('[database VWG.xlsm]database'!$CF$2:$CF$5000>0)*(INDIRECT("'[database VWG.xlsm]database'!"&Y9&"2:$CF$5000"))*('[database VWG.xlsm]database'!$N$2:$N$5000=AC12)*('[database VWG.xlsm]database'!$V$2:$V$5000>$Z$3)*('[database VWG.xlsm]database'!$V$2:$V$5000<$Z$4))

 

[spaarie]

Ik snap hem niet helemaal, maar wellicht zo?

(INDIRECT("'[database VWG.xlsm]database'!"&Y9&"2:$CF$5000")>0)

 

[Egbert12345]

Je begreep het goed. Deze oplossing had ik nu net niet geprobeerd. Thx