Forum Discussion
Solved
VLOOKUP with SUM function
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
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.
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.
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: LunchI want on the result sheet:
ID Ontbijt Lunch Diner Totaal WX1109076 0 2 2 4 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.
