Summe mit Bedingungen bilden

[becks56]

Hallo zusammen!
Ich bin neu hier und habe in der Suche noch keine Lösung meines Problems gefunden...

Ich habe eine Fortbildungstabelle die bestimmte Fortbildungen summieren soll und gleichzeitig die hinterlegte Fortbidlungsdauer in Std.

Also derzeit habe ich folgenden Aufbau:
- Oben die Fortbildungstehemen von links nach rechts mit hinterlegten Stundenzahlen in Zeile darunter
- Links die Mitarbeiternamen und dann in der Spalte des Fortbildungsthemas eine 1 wenn teilgenommen

Nun habe ich Pflichtfortbildungstunden und freiwillige Fortbildungsstunden als Spalten auf der rechten Seite. Hier sollen die Stunden summiert werden.

Meine Idee ist die Pflichtfortbildung als 1 zu kennzeichnen und die freiwilligen als 2. In der Summe sollten dann alle Zellen mit z.B. der 1 die oben eingegebenen Stunden "abgreifen" und diese dann Summieren.

Heißt Mitarbeiter X hat in 2017 an dieser und jener Fobi (mit 1 markiert) teilgenommen und diese "1" ist mit X Stunden hinterlegt. In Summe hat er XY Stunden Fortbildungen besucht.
Ich benötige eine Formel wo ich bei hinzufügen einer weiteren Fortbildung einfach den Bereich erweitern kann und entsprechend die Formel kopieren kann.

Ich hoffe es ist klar geworden was ich möcht und hoffe ihr könnt mir helfen...

P.S.: vba oder Makro kann ich nicht...

Danke schonmal!

[Jonas0806]

Hallo,

das sollte mit Formeln machbar sein. Lade doch bitte mal eine, falls nötig anonymisierte, Beispieldatei hoch.

Das erspart weitere Erklärungen und Anpassungen im Nachhinein.

[BoskoBiati]

Hi,

SUMMEWENN(S) oder SUMMENPRODUKT wären wohl hier hilfreich!

[becks56]

Anbei mal eine Beispieldatei. Das ist der derzeitige Stand an dem ich nicht weiterkomme...

Die Summenbildung in den letzten 3 Spalten, die sich auf die Stunden beziehen, ist dabei relevant.

Hier habe ich die freiwilligen Fortbildungen noch nicht gesondert gekennzeichnet bei den Mitarbeitern.

[BoskoBiati]

Hi,

z.B. so:

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

3

lfd.

Name

Wochenstunden

Stellenanteil

11. Jan

17. Jan

26. Jan

09. Feb

28. Feb

08. Mrz

15. Mrz

16. Mrz

4

Uhrzeit

13:00 - 15:45h

13:00h - 15:00h

14:15h - 15:45h

13:00h - 15:30h

13:00h - 15:00h

13:00h - 15:30h

13:00h - 15.00h

13:00h - 14:30h

5

Stunden

2,75

2,00

1,50

2,50

2,00

2,50

2,00

2,50

6

Pflicht

FW

7

1

Name, Vorname

4

10,39%

M

1

2,00

2,00

0,00

2

2,00

1

Zelle	Formel
E7	=D7/38,5
Q7	=SUMMENPRODUKT($H$5:$O$5*H7:O7)
R7	=SUMMENPRODUKT(($H$6:$O$6<>"FW")*($H$5:$O$5)*H7:O7)
S7	=Q7-R7
T7	=VERWEIS(E7*100;{0;20;50};{2;4;6})
U7	=T7-S7
V7	=SUMME(H7:P7)

[becks56]

@BoskoBiati

Danke dafür.

Nun war ich wohl noch etwas ungenau in der Beschreibung...
Es kann sein das in einer Fortbildung MA sitzen die diese als Pflicht absolvieren und MA die es freiwillig machen.  Das heißt, das ich dann diese MA rausfiltern muss.
Kann ich die freiwillige Fobi mit einer 2 darstellen und in deiner Formel zu R7 das "FW" durch ebendiese 2 ersetzen? Bekomme ich dann auch das gewünschte Ergebnis?

Vielen Dank und viele Grüße!

[BoskoBiati]

Hi,

probiere es aus.

[Rabe]

Hi,

ich würde es so lösen:
Tabelle1

	H	I	J	K	L	M	N	O	P	Q	R	S
5	2,75	2,00	1,50	2,50	2,00	2,50	2,00	2,50			Pflicht	FW
6											P	F
7		P					P			4,00	4,00	0
8		P		F						4,50	2,00	2,50
9	F			P	F					7,25	2,50	4,75
10							F			2,00	0	2,00
11			P							1,50	1,50	0
12										0	0	0
13					P					2,00	2,00	0
14										0	0	0
15										0	0	0
16							P			2,00	2,00	0

verwendete Formeln
Zelle	Formel	Bereich	N/A
Q7:Q16	=SUMMENPRODUKT((H7:O7<>"")*(H$5:O$5))
R7:R16	=SUMMENPRODUKT(($H7:$O7=$R$6)*($H$5:$O$5))
S7:S16	=Q7-R7

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

geht aber auch mit 1 statt P und 2 statt F