een bereik met samengevoegde cellen optellen

[Gotty]

Beste mensen,

Ik hoop dat ik nog een keer een beroep mag doen op jullie kwaliteiten.

Ik heb een voorbeeldbestandje meegepost met 2 tabbladen.
In beide tabbladen geef ik een overzicht weer van 3 dagen, en per dag is er ruimte voor 3 gerechten.
In de witte cellen 'bovenaan' staat de datum en in de crémekleurige cel kan ik de datum kiezen t/m wanneer ik boodschappen wil doen. Omdat ik er ook voor kan kiezen om boodschappen te doen voor álle ingeplande gerechten in het bestand, is "laatste dag" ook een menukeuze en dat heb ik er hier maar even in gelaten.
De totaal benodigde hoeveelheid per ingrediënt staat in de geel/oranje achtige kolom, daar staat nu een "6" en dat is dan 6 gr of ml van een bepaald product maar het gaat nu even om het getal.
In de rode rij geef ik weer of het een "rest(je)" is, (ook wel kliekjes genoemd) ik maak daar onderscheid tussen omdat ik voor een ingepland kliekje geen boodschappen hoef te doen.
In de blauwe rij geef ik aan of het gerecht bereid is, immers als een gerecht niet bereid is ben ik nog het e.e.a nodig (afhankelijk van wat ik in huis heb).
In het groene deel voer ik in wat ik nodig heb aan ingrediënten in gram of mililiter, in 2 van de 3 originele tabbladen haal ik dit uit een database (dat zijn dus formules), en in de derde kan ik het zelf invoeren.

In het tabblad sommen.als maak ik zoals de naam al doet vermoeden gebruik van sommen.als in de geel/oranje achtige kolom. Dit is ook wat ik tot nu toe heb in het originele bestand, de formule is alleen 2 keer zo lang en 21 regels (zoals je hier ziet maak ik graag gebruik van verschillende regels om de boel beter te scheiden en overzichtelijker te maken), dus daar zou ik graag een alternatief voor willen.

In het tabblad somproduct heb ik er een hulprij bij gemaakt zodat ik in de geel/oranje achtige kolom gebruik kan maken van het veel kortere somproduct, een ander verschil is dat in dit tabblad de ruimte voor 1 gerecht 2 kolommen breed is zodat ik wat meer cellen heb om informatie weer te geven. Deze heb ik in dit voorbeeld weggelaten.

Ik heb een tijdje terug heb ik gelezen dat somproduct niet werkt met samengevoegde cellen, dus ik kan de hier gebruikte somproductformule niet gebruiken in het tabblad sommen.als, de hulprij kan ik overzetten, maar voor de ingrediënten blijft gelden dat 1 ingrediënt wordt weergegeven per 2 samengevoegde cellen. Dus dat werkt niet.

Heeft iemand een oplossing om somproduct toch in het tabblad sommen.als te krijgen, of heeft iemand een totaal andere oplossing, waardoor de formule kleiner wordt dan het origineel, het origineel is zowat een pagina groot dus ik denk dat daar wel wat winst valt te behalen, voor jullie beeldvorming wil ik er wel een schermafbeelding van posten, maar ik weet niet of jullie daar wat aan hebben.

Ik hoop dat ik het duidelijk heb uitgelegd. Bij voorbaat bedankt voor het meedenken.

 

[JEC.]

Bij mij werkt de somproductformule prima. Wel de hulprij overgezet natuurlijk

 

[AlexCEL]

De SOM functie iets korter geschreven:

B5: =(B$2<>"laatste dag")*SOM((E$2<=B$2)*(E$3:J$3<>"rest")*(E$4:J$4="")*E5:J5;(L$2<=B$2)*(L$3:Q$3<>"rest")*(L$4:Q$4="")*L5:Q5;(S$2<=B$2)*(S$3:X$3<>"rest")*(S$4:X$4="")*S5:X5)

 

[Gotty]

Dankjewel JVeer, voor je motiverende tekst. Opeens lijkt het hier nu ook te werken. Maar de oplossing van AlexCEL ga ik ook nog onderzoeken zodat ik wat bij kan leren. Zodra ik zeker weet dat ik eruit ben zet ik de vraag op opgelost.

 

[Gotty]

Ik ben er nog niet helemaal uit, daarom heb ik het voorbeeld bestand meer aangepast naar het echte bestand met in boodschappenlijst!S41 en daaronder correcte celverwijzingen en naambereiken.

Voor diegene die het voorgaande niet heeft meegekregen:
Ik heb hier een database met gerechten en bijbehorende ingrediënten met een autofilter, omdat in het echte bestand nog meer info staat waar ik op wil kunnen sorteren om de boel bij te werken.
Een boodschappenlijst met een autofilter zodat wanneer ik een ingrediënt zoek kan ik sorteren op ingrediënt, maar voordat ik boodschappen ga doen sorteer ik op winkel. En ik kan kiezen tot wanneer ik boodschappen wil doen.
Tot slot heb ik een gerechtbereiding, hier kan ik gerechten inplannen door middel van het kiezen van een soort gerecht in de rode rij, en dan de naam van het gerecht die behoren tot dat soort gerecht in de witte rij. Als ik een gerecht gegeten heb geef ik dat aan in de blauwe rij.

Omdat ik de database kan sorteren (terwijl deze statisch gekoppeld is aan de gerechtbereiding), maar ook de boodschappenlijst, wordt het ingrediënt in de boodschappenlijst gezocht in de database, en vanaf de juiste rij in de gerechtbereiding is het bereik dan 20 kolommen breed. Ik heb in mijn eigen bestand nog een tabblad genaamd gerechtbereidingPLUS waar ik zelf hoeveelheden kan invoeren, dus deze heb hier ook maar even simpel ingezet om te laten zien dat de somproduct met verschuiving en vergelijken daar wel werkt maar er moet een getal staan, of niets maar dan ook geen formule zo lijkt het.

En dan verder:
Wat ik nog niet had uitgelegd is dat de datum automatisch opschuift op per week, maar dat heb ik hier weggelaten. Als ik een gerecht heb gekozen, worden de ingrediënten weergegeven, de cellen met ingrediënten die ik niet nodig heb lijken leeg te blijven, maar ik heb hier gekozen voor aangepaste celeigenschappen met 0,000;-0,000;; (en dus ook 3 decimalen in 1 van de andere opties) zodat de nul verborgen wordt. En hier zit de fout in denk ik. Op de een of andere manier wil somproduct met de som 3+2+0 niet uitkomen op 5.

Wat ik geprobeerd heb:
Ik had nog gehoopt dat ik cellen met nul kon uitsluiten, maar dat lukt mij niet, zie daarvoor boodschappenlijst!U47:V47.
In boodschappenlijst!W47 staat nog een gefaalde oplossing waarmee ik alles wat geen waarde heeft ("") uitsluit.
Wat ik ook nog hoopte is een alternatieve formule dat als het ingrediënt niet nodig is de cel leeg blijft (""), maar helaas. Deze formule heb ik nog even in gerechtbereiding!E60 gezet.

Ik hoop zo van harte dat iemand een idee heeft dat ik iets kan toevoegen in somproduct waardoor die de nul overslaat ofzo?

 

[Gotty]

Ik heb dit topic ook nog gevonden, en als ik de sterretjes tussen de voorwaarden wijzig voor een sterretje krijg ik een nul, dus dat is al iets, en als ik dan ook nog 2 streepjes zet links van het argument, lijkt de formule het te doen... Wordt vervolgd.

boodschappenlijst!W48:

=ALS(R$45<>"laatste dag";SOMPRODUCT(
--(d_0501_gb_bereik_datum<=R$45);
--(d_09_gb_bereik_soort_gerecht<>"rest");
--(d_11_gb_bereik_bereid_rest_gegeten="");
(VERSCHUIVING(gerechtbereiding!E$47;VERGELIJKEN(R48;b_19_db_bereik_ingredientnaam;0);0;1;20)));0)

Edit: En nou weet ik nagenoeg zeker dat deze ook nog werkt, kan iemand dat verklaren? ik vind geen goede info over die streepjes

=ALS(R$45<>"laatste dag";SOMPRODUCT(
--(d_0501_gb_bereik_datum<=R$45);
--(d_09_gb_bereik_soort_gerecht<>"rest");
--(d_11_gb_bereik_bereid_rest_gegeten="");
(VERSCHUIVING(gerechtbereiding!E$47;VERGELIJKEN(R48;b_19_db_bereik_ingredientnaam;0);0;1;20)));0)

 

[HSV]

ik vind geen goede info over die streepjes

De streepjes zetten tekst-waarden om in getallen en WAAR en ONWAAR om in respectievelijk 1 en 0.

 

[Gotty]

Hoi HSV,

Dan dan valt alles dus op zijn plaats, bedankt voor je bijdrage!

Bedankt allen, vraag is opgelost!