Excel formule/macro

[afterburn]

Ik probeer wat calculaties te doen, en op zich lukt dat goed alleen is het niet echt handig en universeel. Ik heb de volgende formule:

code:

SUMIFS('[naca_uren_2022.xlsb]2022'!$L:$L,\
'[naca_uren_2022.xlsb]2022'!$A:$A,$C$1,\
'[naca_uren_2022.xlsb]2022'!$J:$J,">=" & C75,\
'[naca_uren_2022.xlsb]2022'!$J:$J,"<=" & D75,\
'[naca_uren_2022.xlsb]2022'!$H:$H,F3)

Wat dit doet:
Ik heb een lijst met gewerkte uren door personen in kolom L, en daarvoor kijk ik kolom A naar een bepaalde project code uit C1, en in kolom J voor een weeknummer tussen bepaalde waardes, voor een activiteiten code in kolom H, die ik aangeef in F3. Ouput is een totaal aantal uren voor code F3 over een periode van 4 weken.

Probleem:
Ik kan meerdere activiteiten codes hebben, die dan staan in F3, F4, F5 enz. voor een bepaalde groep. Ik kan deze code netzoveel keer achter elkaar zetten en optellen als er codes staan, maar dat is niet handig. Ik kan hier geen range aangeven voor F, aangezien het bronbestand ook lege cellen bevat en de getallen dan niet meer kloppen

Wat ik dus zou willen is:
1) een range kunnen specificeren met codes in kolom F, zonder lege cellen mee te nemen
2) bij meerdere codes automatisch alles bij elkaar op te tellen

Maar ik ben geen Excel ridder en loop vast. Iemand hints en suggesties voor een oplossing? Hoeft niet voorgekauwd te zijn (mag wel ), maar als ik genoeg info/hints kan vinden/krijgen om zelf een oplossing te verzinnen is ook prima. Leren we ook nog wat.

EDIT: mogelijke verduidelijking: ik moet dit meermaals doen voor meerdere groepen. Die staan dan in G, H, enz. Het kan zijn dat ik in F 3 codes heb die opgezocht en opgeteld moeten worden, in G 1 en in H 6. Deze formule boven bv 10 bij elkaar tellen zodat ik rij 1:10 afvang voor kolommen F tot H gaat fout op het moment dat er lege cellen zijn. Dus moet ik voor elke kolom alles aanpassen. Niet handig.

[ Voor 11% gewijzigd door afterburn op 08-04-2022 11:29 ]

[Dunka]

Wat je zou kunnen doen is deze formule per activiteitscode in Excel te zetten, maar dan dit resultaat alleen over te nemen als de rij in kolom F niet leeg is.

Als je de SUMIFS in kolom G zet, kan je dit met de volgende formule in kolom H zetten (Nederlandse versie Excel):

code:

=ALS(isleeg(F2);0;G2)

.

De som van de waarden in kolom H zou nu je gewenste uitkomst moeten geven.

[afterburn]

Dunka schreef op vrijdag 8 april 2022 @ 11:57:
Wat je zou kunnen doen is deze formule per activiteitscode in Excel te zetten, maar dan dit resultaat alleen over te nemen als de rij in kolom F niet leeg is.

Als je de SUMIFS in kolom G zet, kan je dit met de volgende formule in kolom H zetten (Nederlandse versie Excel):

code:

=ALS(isleeg(F2);0;G2)

.

De som van de waarden in kolom H zou nu je gewenste uitkomst moeten geven.

Kolom H is brondata die ingelezen en opgeteld wordt. Dit is bestaande data, niet wat ik hoef te maken. Ik doe een aantal selecties waarna ik binnen die selectie de waardes van kolom H bij elkaar optel en wegschrijf.

project	weeknr	activiteit	persoon	aantal
7890	1	xyz	pietje	5
1234	1	abc	klaasje	10
1234	1	klm	klaasje	10
1234	1	abc	pietje	10
1234	2	abc	pietje	7
1234	1	xyz	pietje	5
1234	3	xyz	klaasje	5

Voorbeeld met tabel: wat is uit project 1234 het totaal van wk1 voor activiteiten abc en xyz. (In dit geval dus 25)

[Microkid]

Kan je niet beter een draaitabel maken? Dan kan je filteren op project en dan de totalen per activiteit per week laten zien. Veel makkelijker dan formules.

Edit: even quick&dirty in Google Sheets nagebouwd:

[ Voor 56% gewijzigd door Microkid op 08-04-2022 13:51 ]

[afterburn]

Microkid schreef op vrijdag 8 april 2022 @ 13:47:
Kan je niet beter een draaitabel maken? Dan kan je filteren op project en dan de totalen per activiteit per week laten zien. Veel makkelijker dan formules.

Dat zou kunnen, echter de lijst die ik hiermee probeer te maken is weer input data voor bladen in hetzelfde werkboek waar ik dan ook weer mee moet rekenen en doen. En dat is wat lastig met een pivottable volgens mij.

[Icephase]

afterburn schreef op vrijdag 8 april 2022 @ 13:50:
[...]

Dat zou kunnen, echter de lijst die ik hiermee probeer te maken is weer input data voor bladen in hetzelfde werkboek waar ik dan ook weer mee moet rekenen en doen. En dat is wat lastig met een pivottable volgens mij.

Nee hoor, in de verwijzing komt de formule =DRAAITABEL.OPHALEN te staan, en die past zich dus aan aan de inhoud van de draaitabel. Wel wordt het lastig als een bepaalde combinatie dan helemaal niet voorkomt in de draaitabel, dus niet teveel filteren en samenvoegen.

[Dunka]

afterburn schreef op vrijdag 8 april 2022 @ 13:43:
[...]

Kolom H is brondata die ingelezen en opgeteld wordt. Dit is bestaande data, niet wat ik hoef te maken. Ik doe een aantal selecties waarna ik binnen die selectie de waardes van kolom H bij elkaar optel en wegschrijf.

Kolom H in het blad waar je de berekeningen doet, niet kolom H in het gegevensblad.

[Dunka]

	F	G	H
2	XYZ	=SUMIFS...	=ALS(isleeg(F2);0;G2)
3	ABC	=SUMIFS...	=ALS(isleeg(F3);0;G3)

[afterburn]

Icephase schreef op vrijdag 8 april 2022 @ 13:53:
[...]


Nee hoor, in de verwijzing komt de formule =DRAAITABEL.OPHALEN te staan, en die past zich dus aan aan de inhoud van de draaitabel. Wel wordt het lastig als een bepaalde combinatie dan helemaal niet voorkomt in de draaitabel, dus niet teveel filteren en samenvoegen.

Wist ik niet, ga ik eens mee spelen. Dank.

[Lustucru]

Om je oorspronkelijke vraag te beantwoorden: dat los je op met een matrixformule:

Zoals je ziet is het criterium voor het criteriumbereik B1:B7 zelf ook een bereik. Het resultaat van de matrixvariant van sommen.als (bevestigen met ctrl-shift-enter) is dus een een bereik van evenveel sommen.als als er in het criteriumbereik staan.

En daar neem je dan weer de som van.

[DirtyBird]

Je zou ook een extra tabblad kunnen maken waar je met de FILTER-functie alle lege cellen afvangt.
Vervolgens pas je je macro toe op dat tabblad.