Returning multiple values

[Guydmj]

Dear readers,

For my work I need to make a excell sheet that returns multiple values from a list of dates as sum and as individual (original) rows. Since there are over 6000 rows of data I would like to make a formula that automates this process.

I know that what I am asking is pretty difficult and very hard to make however, I hope with your help I can make it.

Below you will see a small overview of what the original data looks like.

As you can see there are multiple equal dates in the date collumn that I would like to return in a seperate sheet as a sum. so i.e. 01-01-2016 shows up 9 times in the original dataset "1". I would like to return all the values in the collums behind the date 01.01.2016 as a sum. So if 01-01-2016 has in collumn "Pax" with data, 2,7,15,200 placed in different rows, they need to be shown in the collumn pax total row 01.01.2016, 224=2+7+15+200.
Additionally I would like the text collums to give me a number of times a "text" value shows up so i.e. "veranstaltung" shows up twice which would be returned as "2", "Aufbau" shows up once which would be returned as "1" these can be shown in a new collumn.

I have tried things like vlookup etc index match however they only return the values linked to the one date or lookup value, what I want my new sheet to look like is a number of dates "01-01-2016","02-01-2016","03-01-2016" etc. and then show the sum of the values linked to that date.

Lastly I would like another seperate set of collumns where all the original data that appear for a specific date such as 01-01-2016 are returned. preferable as shown at the bottom.


Data set 1:
[TABLE="width: 2112"]
<tbody>[TR]
[TD="class: xl64, width: 88"]Datum[/TD]
[TD="class: xl64, width: 88"] Einzelsaal-/
Kombisaal[/TD]
[TD="class: xl64, width: 88"] Art der
,Verwendung[/TD]
[TD="class: xl64, width: 88"] Bestuhlungsart[/TD]
[TD="class: xl64, width: 88"] Name
Nutzers[/TD]
[TD="class: xl64, width: 88"] Umstzsteuer
-frei-pflichtig[/TD]
[TD="class: xl64, width: 88"] Veranstaltungs
Begin Uhrzeit[/TD]
[TD="class: xl64, width: 88"] Veranstaltungs
Ende Uhrzeit[/TD]
[TD="class: xl64, width: 88"] Ust-Pflichtig
Anzahl Stunden[/TD]
[TD="class: xl64, width: 88"] UST-frei
Anzahl Stunden[/TD]
[TD="class: xl64, width: 88"] pax[/TD]
[TD="class: xl64, width: 88"] Anzahl
Cover[/TD]
[TD="class: xl64, width: 88"]RN[/TD]
[TD="class: xl64, width: 88"] Raum-Mieten[/TD]
[TD="class: xl64, width: 88"] Austellungs-mieten[/TD]
[TD="class: xl64, width: 88"] Bereitstellungs-kosten[/TD]
[TD="class: xl64, width: 88"] Tagungs-technik[/TD]
[TD="class: xl64, width: 88"] Tagungs-pauschale[/TD]
[TD="class: xl64, width: 88"] Speisen Getränke Kaffee[/TD]
[TD="class: xl64, width: 88"] Sonstiger F&B Umsatz[/TD]
[TD="class: xl64, width: 88"] Logis
Rev[/TD]
[TD="class: xl64, width: 88"] Gesamt Umsatz[/TD]
[TD="class: xl64, width: 88"] Erstell Datum[/TD]
[TD="class: xl64, width: 88"] Leadtime multi[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01.01.2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Veranstaltung[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]USt-pflichtig[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: right"]00.01.1900[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01.01.2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]USt-pflichtig[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: right"]00.01.1900[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01.01.2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]USt-pflichtig[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: right"]00.01.1900[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01.01.2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]USt-pflichtig[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: right"]00.01.1900[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01.01.2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Veranstaltung[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]USt-pflichtig[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: right"]00.01.1900[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01.01.2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]USt-pflichtig[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: right"]00.01.1900[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01.01.2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]USt-pflichtig[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: right"]00.01.1900[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01.01.2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Aufbau[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]USt-pflichtig[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: right"]00.01.1900[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]



Sum from Dataset 1 Sum from dataset 2 Individual rows of data
from dataset 2
-------------------------------------------------------------------------------------------------------------------------------------------------------
[TABLE="width: 1408"]
<tbody>[TR]
[TD="width: 88"]Datum[/TD]
[TD="width: 88"]Pax[/TD]
[TD="width: 88"]RN[/TD]
[TD="width: 88"]Leadtime[/TD]
[TD="class: xl65, width: 88"]Rev[/TD]
[TD="width: 88"]Datum[/TD]
[TD="width: 88"]Pax[/TD]
[TD="width: 88"]RN[/TD]
[TD="width: 88"]Leadtime[/TD]
[TD="width: 88"]Rev[/TD]
[TD="class: xl65, width: 88"][/TD]
[TD="width: 88"]Datum[/TD]
[TD="width: 88"]Pax[/TD]
[TD="width: 88"]RN[/TD]
[TD="width: 88"]Leadtime[/TD]
[TD="class: xl65, width: 88"]Rev[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01.01.2016[/TD]
[TD] 224[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="class: xl65"] 0[/TD]
[TD]01.01.2016[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="class: xl65"][/TD]
[TD]01.01.2016
01.01.2016
etc. (all data from 01.01.2016)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]


I hope my explanation is clear and someone can help me out.

Kind regards,

Guy

 

[AhoyNC]

Maybe something like this

Excel Workbook
	A	B	C	K	L	M
1	Datum	Einzelsaal-/	Art der	pax	Anzahl	RN
2		Kombisaal	,Verwendung		Cover
3	01.01.2016	0	Veranstaltung	2	0	0
4	01.01.2016	0	0	0	0	0
5	01.01.2016	0	0	0	0	0
6	01.01.2016	0	0	7	0	0
7	01.02.2016	0	Veranstaltung	300
8	01.01.2016	0	Veranstaltung	0	0	0
9	01.01.2016	0	0	15	0	0
10	01.01.2016	0	0	200	0	0
11	01.01.2016	0	Aufbau	0	0	0
Sheet1

For sum and count

Excel Workbook
	A	B	C	D
1	Datum	Pax	Veranstaltung	Aufbau
2	01.01.2016	224	2	1
3	01.02.2016	300	1	0
Sheet

For list:
NOTE: formula for ROW ID is an array formula and must be enter with CTRL-SHIFT-ENTER.
Drag formula down as needed.
Formula in B5 can just be copied down and across.

Excel Workbook
	A	B	C	D
1	Datum	01.01.2016
2	# of Records	8
3
4	Row ID	Datum	Pax	Art der
5	1	01.01.2016	2	Veranstaltung
6	2	01.01.2016	0	0
7	3	01.01.2016	0	0
8	4	01.01.2016	7	0
9	6	01.01.2016	0	Veranstaltung
10	7	01.01.2016	15	0
11	8	01.01.2016	200	0
12	9	01.01.2016	0	Aufbau
Sheet3

 

[Guydmj]

Thank you very much! That helps a lot.

Especially the last part I could not complete myself.

I have found another way in the mean time to do the "#" calculations: SumIf, this to me looks like an easier way with less margin for error.

Any way thank you for the help!

 

[Guydmj]

For some reason I cannot get the Row ID column to work could you please double check the forumulas That I have put in? I am working with a German version of Excel to make life easy however, here is a translation website: https://www.scheidgen.de/Excel/VBA/tabellenfunktionen.htm

Sheet 1: Request Mary filtered
[TABLE="width: 2506"]
<tbody>[TR]
[TD]Filters[/TD]
[TD]Teilnehmerzahl (maximum)[/TD]
[TD]>1<401[/TD]
[TD]Anzahl Zimmer (max.)[/TD]
[TD]>1<201[/TD]
[TD]Leadtime[/TD]
[TD]>9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Request No.[/TD]
[TD]Grund[/TD]
[TD]Details Grund[/TD]
[TD]Source Code[/TD]
[TD]Quote No[/TD]
[TD]Haus[/TD]
[TD]Datum[/TD]
[TD]Bundesland (GEO)[/TD]
[TD]Abreise[/TD]
[TD]Account Owner[/TD]
[TD]Inhaber: Vollständiger Name[/TD]
[TD]Stadt (GEO)[/TD]
[TD]Anzahl Übernachtungen[/TD]
[TD]Anzahl Zimmer (max.)[/TD]
[TD]Teilnehmerzahl (Summe)[/TD]
[TD]Teilnehmerzahl (maximum)[/TD]
[TD]Market Code[/TD]
[TD]Market Segment[/TD]
[TD]Accountname[/TD]
[TD]Name of Booking[/TD]
[TD]R-Status[/TD]
[TD]Account No.[/TD]
[TD]Letzte Statusänderung[/TD]
[TD]Zuletzt geändert am[/TD]
[TD]Zuletzt geändert am[/TD]
[TD]Erstelldatum[/TD]
[TD]Q-Status[/TD]
[TD]Leadtime[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]BAN - Banquet Capacity (too low)[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]04.01.2016[/TD]
[TD]x[/TD]
[TD]08.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]12[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]LOS[/TD]
[TD]x[/TD]
[TD]21.07.2015 09:14[/TD]
[TD]14.12.2015[/TD]
[TD]21.07.2015[/TD]
[TD]16.07.2015[/TD]
[TD]UNC[/TD]
[TD="align: right"]172[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]UNK - Unknown[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]08.01.2016[/TD]
[TD]x[/TD]
[TD]11.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]365[/TD]
[TD]300[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]LOS[/TD]
[TD]x[/TD]
[TD]26.08.2016 10:20[/TD]
[TD]26.08.2016[/TD]
[TD]26.08.2016[/TD]
[TD]02.10.2015[/TD]
[TD]LOS[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]BAN - Banquet Capacity (too low)[/TD]
[TD]x[/TD]
[TD]TA - Travel Agent[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]11.01.2016[/TD]
[TD]x[/TD]
[TD]12.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]UNC[/TD]
[TD]x[/TD]
[TD]22.12.2015 11:24[/TD]
[TD]22.12.2015[/TD]
[TD]22.12.2015[/TD]
[TD]22.12.2015[/TD]
[TD]UNC[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]BAN - Banquet Capacity (too low)[/TD]
[TD]x[/TD]
[TD]TA - Travel Agent[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]14.01.2016[/TD]
[TD]x[/TD]
[TD]15.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]36[/TD]
[TD]18[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]UNC[/TD]
[TD]x[/TD]
[TD]15.12.2015 14:19[/TD]
[TD]15.12.2015[/TD]
[TD]15.12.2015[/TD]
[TD]15.12.2015[/TD]
[TD]UNC[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]LOC - Location[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]15.01.2016[/TD]
[TD]x[/TD]
[TD]16.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]61[/TD]
[TD]61[/TD]
[TD]73[/TD]
[TD]73[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]LOS[/TD]
[TD]x[/TD]
[TD]07.01.2016 13:07[/TD]
[TD]07.01.2016[/TD]
[TD]07.01.2016[/TD]
[TD]04.01.2016[/TD]
[TD]LOS[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]RAH - Rate Too High[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]16.01.2016[/TD]
[TD]x[/TD]
[TD]17.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]LOS[/TD]
[TD]x[/TD]
[TD]21.12.2015 11:45[/TD]
[TD]21.12.2015[/TD]
[TD]21.12.2015[/TD]
[TD]21.12.2015[/TD]
[TD]LOS[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]LOC - Location[/TD]
[TD]x[/TD]
[TD]TA - Travel Agent[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]18.01.2016[/TD]
[TD]x[/TD]
[TD]20.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]35[/TD]
[TD]35[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]LOS[/TD]
[TD]x[/TD]
[TD]04.01.2016 14:07[/TD]
[TD]04.01.2016[/TD]
[TD]04.01.2016[/TD]
[TD]21.12.2015[/TD]
[TD]LOS[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]TA - Travel Agent[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]18.01.2016[/TD]
[TD]x[/TD]
[TD]19.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]DEF[/TD]
[TD]x[/TD]
[TD]05.01.2016 09:45[/TD]
[TD]05.02.2016[/TD]
[TD]05.01.2016[/TD]
[TD]10.12.2015[/TD]
[TD]DEF[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]CDB - Double Booking[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]19.01.2016[/TD]
[TD]x[/TD]
[TD]21.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]140[/TD]
[TD]70[/TD]
[TD]140[/TD]
[TD]70[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]CAN[/TD]
[TD]x[/TD]
[TD]23.11.2015 12:00[/TD]
[TD]23.11.2015[/TD]
[TD]23.11.2015[/TD]
[TD]23.11.2015[/TD]
[TD]CAN[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]UNK - Unknown[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]19.01.2016[/TD]
[TD]x[/TD]
[TD]21.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]DEF[/TD]
[TD]x[/TD]
[TD]31.07.2015 08:09[/TD]
[TD]31.07.2015[/TD]
[TD]31.07.2015[/TD]
[TD]29.07.2015[/TD]
[TD]LOS[/TD]
[TD="align: right"]174[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]BAN - Banquet Capacity (too low)[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]19.01.2016[/TD]
[TD]x[/TD]
[TD]21.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]140[/TD]
[TD]70[/TD]
[TD]140[/TD]
[TD]70[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]UNC[/TD]
[TD]x[/TD]
[TD]23.11.2015 13:48[/TD]
[TD]23.11.2015[/TD]
[TD]23.11.2015[/TD]
[TD]23.11.2015[/TD]
[TD]UNC[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]19.01.2016[/TD]
[TD]x[/TD]
[TD]21.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]24[/TD]
[TD]12[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]DEF[/TD]
[TD]x[/TD]
[TD]03.03.2015 16:26[/TD]
[TD]03.03.2015[/TD]
[TD]03.03.2015[/TD]
[TD]02.03.2015[/TD]
[TD]DEF[/TD]
[TD="align: right"]323[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]COP - Change Of Profile[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]20.01.2016[/TD]
[TD]x[/TD]
[TD]22.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]LOS[/TD]
[TD]x[/TD]
[TD]10.12.2015 11:27[/TD]
[TD]10.12.2015[/TD]
[TD]10.12.2015[/TD]
[TD]01.12.2015[/TD]
[TD]LOS[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]BAN - Banquet Capacity (too low)[/TD]
[TD]x[/TD]
[TD]AGE - Agentur MICE[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]20.01.2016[/TD]
[TD]x[/TD]
[TD]21.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]125[/TD]
[TD]125[/TD]
[TD]250[/TD]
[TD]125[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]UNC[/TD]
[TD]x[/TD]
[TD]10.11.2015 15:04[/TD]
[TD]10.11.2015[/TD]
[TD]10.11.2015[/TD]
[TD]10.11.2015[/TD]
[TD]UNC[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]IMC - Internal Meeting Cancelled[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]21.01.2016[/TD]
[TD]x[/TD]
[TD]22.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]82[/TD]
[TD]82[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]LOS[/TD]
[TD]x[/TD]
[TD]09.08.2016 11:56[/TD]
[TD]09.08.2016[/TD]
[TD]09.08.2016[/TD]
[TD]07.09.2015[/TD]
[TD]LOS[/TD]
[TD="align: right"]136[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]21.01.2016[/TD]
[TD]x[/TD]
[TD]22.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]DEF[/TD]
[TD]x[/TD]
[TD]15.07.2015 11:01[/TD]
[TD]27.11.2015[/TD]
[TD]15.07.2015[/TD]
[TD]15.07.2015[/TD]
[TD]DEF[/TD]
[TD="align: right"]190[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]LOC - Location[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]25.01.2016[/TD]
[TD]x[/TD]
[TD]26.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]LOS[/TD]
[TD]x[/TD]
[TD]21.01.2016 20:13[/TD]
[TD]21.01.2016[/TD]
[TD]21.01.2016[/TD]
[TD]11.01.2016[/TD]
[TD]LOS[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]CDA - Change of Date[/TD]
[TD]x[/TD]
[TD]COM - Company[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]25.01.2016[/TD]
[TD]x[/TD]
[TD]26.01.2016[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]UNC[/TD]
[TD]x[/TD]
[TD]08.01.2016 15:44[/TD]
[TD]08.01.2016[/TD]
[TD]08.01.2016[/TD]
[TD]11.12.2015[/TD]
[TD]LOS[/TD]
[TD="align: right"]45[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2: Tabelle2

[TABLE="width: 2625"]
<tbody>[TR]
[TD]Datum[/TD]
[TD]18.01.2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]# of records[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row ID[/TD]
[TD]Request No.[/TD]
[TD]Grund[/TD]
[TD]Details Grund[/TD]
[TD]Source Code[/TD]
[TD]Quote No[/TD]
[TD]Haus[/TD]
[TD]Datum[/TD]
[TD]Bundesland (GEO)[/TD]
[TD]Abreise[/TD]
[TD]Account Owner[/TD]
[TD]Inhaber: Vollständiger Name[/TD]
[TD]Stadt (GEO)[/TD]
[TD]Anzahl Übernachtungen[/TD]
[TD]Anzahl Zimmer (max.)[/TD]
[TD]Teilnehmerzahl (Summe)[/TD]
[TD]Teilnehmerzahl (maximum)[/TD]
[TD]Market Code[/TD]
[TD]Market Segment[/TD]
[TD]Accountname[/TD]
[TD]Name of Booking[/TD]
[TD]R-Status[/TD]
[TD]Account No.[/TD]
[TD]Letzte Statusänderung[/TD]
[TD]Zuletzt geändert am[/TD]
[TD]Zuletzt geändert am[/TD]
[TD]Erstelldatum[/TD]
[TD]Q-Status[/TD]
[TD]Leadtime[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]{=IF(ROWS($A$5:A5)>$B$2,"",SMALL(IF(Sheet1!$A$3:$A$11=Sheet3!$B$1,ROW(Sheet1!$A$3:$A$11)-ROW(Sheet1!$A$3)+1),ROWS($A$5:A5)))}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In sheet Tabelle2 Cell B2 this is the formula: which is working
=ZÄHLENWENN('Request mary filtered'!$G$3:$G$2490;Tabelle2!$B$1)

In sheet Tabelle2 Cell A5 this is the formula that I have made:
{=WENN(ZEILE($A5:A5)>$B$2;"";KKLEINSTE(WENN('Request mary filtered'!$G$3:$G$2490=$B$1;ZEILE('Request mary filtered'!$G$3:$G$2490)-ZEILE('Request mary filtered'!$G$3)+1);ZEILE($A5:A$6)))}

And in sheet Tabelle2 cell B5 this is the formula:
=WENN($A5="";"";INDEX('Request mary filtered'!$A$2:$AB$2;Tabelle2!$A5;VERGLEICH(Tabelle2!B$4;'Request mary filtered'!$A$2:$AB$2)))

I can not find the problem however, for some reason the cells do not show any results.

 

[AhoyNC]

With the count and sum sheet I would agree that the SUMIF and COUNTIFS functions are the better way to go. Just didn't know which ver, of Excel you had and SUMPRODUCT will; work in any ver.

So based on the example above sheet2 could just be:

Excel Workbook
	A	B	C	D
1	Datum	Pax	Veranstaltung	Aufbau
2	01.01.2016	224	2	1
3	01.02.2016	300	1	0
Sheet2

It looks like the problem with the Row ID might be in 2 places in the formula you are using ROW (ZEILE) and it should be ROWS (ZEILEN)

=WENN(ZEILEN($A5:A5)>$B$2;"";KKLEINSTE(WENN('Request mary filtered'!$G$3:$G$2490=$B$1;ZEILE('Request mary filtered'!$G$3:$G$2490)-ZEILE('Request mary filtered'!$G$3)+1);ZEILEN($A5:A$6)))

 

[Guydmj]

Dear Ahoync;

Thank you for your reply once again.

I am using Excel 2013. The language setting I can not change because I do not own the admin rights.

Unfortunatly the formulas are still not working. The formula in cell A5 returns the value 7 where it should be a 1 if I am not mistaken. When I continue to copy the formula down it from Cell A6 onwards returns the error "#ZAHL" or translated as "#NUM!".

The formula in cell B5 is now retruning an error "#BEZUG!" translated as "#REF!".

Is there a way tha I can upload the document here or somewhere else so you can have a good look at it. I think that would make your and my life a lot easier. (Dropbox, googledocs etc are all blocked by the admin)

Kind regards,

Guy

 

[AhoyNC]

The formula for the ROW ID is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on a MAC). If done right Excel will put brackets "{}" around the formula (at least in the English ver.).
Does your formula show brackets around it the formula bar?
If not put the cursor in the cell with the formula. Press F2 for edit and then press CTRL-SHIFT-ENTER.
Then drag the formula down.

You are probably getting the #REF error because of an invalid ROW ID.

Dropbox or Excel OneDrive with a link would be the best way to send a file, but since you can't do that lets see if you can get the example below to work.

There are 2 rows (row 5 & 9) in the data range that don't meet the criteria. I have left off error checking which is why you see #NUM error at the bottom of the list.
Once you enter the formula in A18 just drag it down the column. I'm showing all the formulas so you can see how it changes as you drag it down.

Excel Workbook
	A	B	C	D	E	F	G
1	Datum	Einzelsaal-/	Art der	pax	Anzahl	RN
2		Kombisaal	,Verwendung		Cover		Row
3	01.01.2016	0	Veranstaltung	2	0	0	1
4	01.01.2016	0	0	0	0	0	2
5	01.01.2016	0	0	0	0	0	3
6	01.01.2016	0	0	7	0	0	4
7	01.02.2016	0	Veranstaltung	300			5
8	01.01.2016	0	Veranstaltung	0	0	0	6
9	01.01.2016	0	0	15	0	0	7
10	01.01.2016	0	0	200	0	0	8
11	01.05.2016	0	0	200	0	0	9
12	01.01.2016	0	Aufbau	0	0	0	10
13	01.01.2017	1	Aufbau	0	0	0	11
14
15
16	Datum:	01.01.2016
17	Row ID
18	1
19	2
20	3
21	4
22	6
23	7
24	8
25	10
26	#NUM !
27	#NUM !
28
Sheet