VLOOKUP with SUM function

[FreddyJayNL]

Hi,

I would like to use vertical search in combination with the sum function in multiple tabs.

The goal is that I can see exactly how many people have eaten in the green tab.

Example:

TAB 12/19/2022

WX1109076

has 1 dinner

TAB 20-12-2022

WX1109076

has 1 diner

TAB Results (green)

WX1109076 has 2 dinner 

Formula:

=SUM(VLOOKUP([@ID];Table19;4;FALSE))

But unfortunately 1 is always standing. How do I solve this?

Thank you in advance.

Yours sincerely,

FreddyJay

[#2]

@FreddyJayNL Perhaps I misunderstand, but your formula only looks up a value in one table. Table19, that is. You'd need to include Table20 (I presume) as well. Something like this:

=VLOOKUP([@ID];Table19;4;FALSE))+VLOOKUP([@ID];Table20;4;FALSE)

No need for a SUM function, by the way. But maybe smarter to not use separate tabs for each date. Put all in one larger table that contains a column for the date. That would probably make it much easier to summarize/analyze the data. Just a suggestion.

[#3]

@Riny_van_Eekelen

There are IDs who have eaten breakfast/lunch/dinner on several days.
I would like to have a formula search for ID and add together what that ID has consumed.

An example:
ID : WX1109076
19-12-2022 and 22-12-2022 : Dinner
19-12-2022 and 21-12-2022: Lunch

I want on the result sheet:

ID	Ontbijt	Lunch	Diner	Totaal
WX1109076	0	2	2	4

[#4 · Accepted solution]

@FreddyJayNL I responded to your PM. Consider to post your file and my answer (be it in Dutch) here for others to see. Or not. That's up to you.

Edit: The accepted solution is to use Power Query to connect to all individual tables, combine them and then Group and Sum by ID for all relevant columns.