Suche passende Formel

[andre150177]

Hallo zusammen,

ich benötige Hilfe bei der Erstellung von 3 Formeln für eine Quartalsabrechnung

in der Spalte B4:B6000 werden die Übernachtungen eingetragen (z.B. 5)
in der Spalte H4:H6000 ist die dazugehörige Anzahl der Gäste (z.B. 3)

in der Spalte I4:I6000 ist das Anreisedatum (z.B. 02.01.2016) eingetragen und
in der Spalte J4:J6000 ist das Abreisedatum (z.B. 07.01.2016) bei 5 Übernachtungen


ich möchte berechnen
1. die Summe der Übernachtungen aus B4:B6000 wenn in der Aufenthalt im 1. Quartal sich befindet
2. die Summe der Gäste aus H4:H6000 die sich im selben Zeitraum zu Gast waren und zum
3. das Summenprodukt wieviele Übernachtungen es im 1.Quartal gesamt waren.

wer kann mir da helfen?

MfG André

[RPP63]

Hallo André!
1. Kennst Du eine Pivot-Tabelle?
2. (anonymisierte) Beispieldatei wäre nicht schlecht!
3. Aufenthalt vom 28.03. - 05.04. ist welches Quartal?

Gruß Ralf

[andre150177]

Hallo André!
1. Kennst Du eine Pivot-Tabelle?
2. (anonymisierte) Beispieldatei wäre nicht schlecht!
3. Aufenthalt vom 28.03. - 05.04. ist welches Quartal?

Gruß Ralf

nein leider kenne ich mich mit einer Pivot-Tabelle noch nicht aus.

hmm da wäre es super wenn die tage 28.03.-31.03. im 1. Quartal berechnet werden könnte und 1.4-5.4. im 2. Quartal dann auftauchen

an bei die excel tabelle

MfG

[RPP63]

Hi!
Immerhin:
4.000 Zeilen mit Nullen!
Erleichtert die Auswertung ungemein!

Ratlos,
Ralf

[andre150177]

Hi!
Immerhin:
4.000 Zeilen mit Nullen!
Erleichtert die Auswertung ungemein!

Ratlos,
Ralf

habe dir mal etwas ausgefüllt.
es wir in der tabelle das komplette jahr fortlaufend mit den meldescheinen bestückt

aus dieser tabelle soll dann Quartalweise eine abrechnung erfolgen.

bei VMT 1.Quartal und VMT 2.Quartal habe ich in die spalten mal die ergebnisse geschrieben die mit der formel erscheinen sollen.
ich hoffe es hilft weiter.

die jahresrechnung in zeile 1-3 ist nur für die statistik wieviele besucher an den einzelnen tagen in der pension waren. kann man außeracht lassen.

mfg Andre

[Rabe]

Hi Andre,

bei VMT 1.Quartal und VMT 2.Quartal habe ich in die spalten mal die ergebnisse geschrieben die mit der formel erscheinen sollen.
ich hoffe es hilft weiter.

ich habe die verbundenen Zellen aufgelöst.
Irgendwie komme ich auf andere Zahlen, vergleiche Deine Zahlen in Zeile 4 mit meinen in Zeile 3, zähle doch nochmal.

Pension

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

1

Erwachsene

2

2

2

gesamt

6

6

verwendete Formeln
Zelle	Formel	Bereich	N/A
O1:AD1	=WENN(SUMMENPRODUKT((O$3:O$3>=$I4:$I6000)*(O$3:O$3<=$J4:$J6000)*$H4:$H6000)=0;"";SUMMENPRODUKT((O$3:O$3>=$I4:$I6000)*(O$3:O$3<=$J4:$J6000)*$H4:$H6000))
O2:AD2	=WENN(SUMMENPRODUKT((O$3:O$3>=$I4:$I6000)*(O$3:O$3<=$J4:$J6000)*$C4:$C6000)=0;"";SUMMENPRODUKT((O$3:O$3>=$I4:$I6000)*(O$3:O$3<=$J4:$J6000)*$C4:$C6000))

VMT 1. Quartal

	A	B	C	D	E	F	G	H	I
1	Abrechnungszeitraum Januar - März 2016						01.01.2016	Erw.	15
2	Beherbergungsbetrieb Nr.	Dauer in Tagen	Anzahl Personen	Übernachtungen	Zweck der Reise		31.03.2016	Kinder	9
3	A	36	24	203					24
4	10001	39	15	123	Urlaub

Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle	Formel	Bereich	N/A
I1	=SUMMENPRODUKT(($G$2>=Pension!$I$4:$I$6000)*($G$1<=Pension!$J$4:$J$6000)*Pension!$H$4:$H$6000)
I2	=SUMMENPRODUKT(($G$2>=Pension!$I$4:$I$6000)*($G$1<=Pension!$J$4:$J$6000)*Pension!$D$4:$E$6000)
B3	=ANZAHL(Pension!O2: DA2)
C3	=I3
D3	=SUMME(Pension!O2: DA2)
I3	=SUMME(I1:I2)

Füllfarben und Muster
Zelle	Rot	Grün	Blau	Color	Muster	Farbe
A2:E2	197	217	241	15849925

Zellen mit Füllfarbe automatisch werden nicht dargestellt

Schriftformate
Zelle	Rot	Grün	Blau	Color	Stil	Unterstreichung	Effekte	Durchgestrichen	Schriftart
A1: C1, A2:E2, D1: D2, F3: H3, F4:I4	0	0	0	0	Fett

Zellen mit Schriftformatierung automatisch werden nicht dargestellt

Excel-Inn.de

Hajo-Excel.de

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

Add-In-Version 21.07 einschl. 64 Bit

"Dauer in Tagen" sind Anzahl belegter Tage?
Übernachtungen zählen doch für jede Person 1 Übernachtung pro Tag, egal ob Erwachsener oder Kind, oder nicht?
Anzahl Personen sind auch Summe von Erwachsenen + Kinder?

[andre150177]

"Dauer in Tagen" sind Anzahl belegter Tage?
Übernachtungen zählen doch für jede Person 1 Übernachtung pro Tag, egal ob Erwachsener oder Kind, oder nicht?
Anzahl Personen sind auch Summe von Erwachsenen + Kinder?

Hallo Rabe,

erst einmal recht vielen Dank für deine Hilfe die hat mir schon sehr weiter geholfen.

Das ist richtig, Übernachtungen zählen je Person 1 Übernachtung pro Tag, für die VMT Abrechnung werden nur die Erwachsenen benötigt da sind die Kinder unrelevant.

MfG André

[Rabe]

Hi Andre,

Das ist richtig, Übernachtungen zählen je Person 1 Übernachtung pro Tag, für die VMT Abrechnung werden nur die Erwachsenen benötigt da sind die Kinder unrelevant.

dann mußt Du ja nur die richtigen Spalten/Zeilen in der Summenprodukt-Formel verwenden:
=SUMMENPRODUKT(($G$2>=Pension!$I$4:$I$6000)*($G$1<=Pension!$J$4:$J$6000)*Pension!$H$4:$H$6000)
in Spalte C (nur die Erwachsenen)
und evtl.
=SUMME(Pension!O1: DA1)
für Spalte D, falls nur die Erwachsenen mitzählen.

Und Du brauchst die Spalten H und I nicht mehr.

[andre150177]

Hi Andre,


dann mußt Du ja nur die richtigen Spalten/Zeilen in der Summenprodukt-Formel verwenden:
=SUMMENPRODUKT(($G$2>=Pension!$I$4:$I$6000)*($G$1<=Pension!$J$4:$J$6000)*Pension!$H$4:$H$6000)
in Spalte C (nur die Erwachsenen)
und evtl.
=SUMME(Pension!O1: DA1)
für Spalte D, falls nur die Erwachsenen mitzählen.

Und Du brauchst die Spalten H und I nicht mehr.

Hallo Rabe,

so habe ich es auch gemacht, danke nochmals für deine Hilfe.

MfG André