How to sum a series of named ranges in Google Sheet

Question

In a Google Sheet, I have two sheets, Diary and Food List. The Food List looks like: I defined Rice and Tomato as named ranges which span from column B to F. The diary looks like: I want to add the information associated to each named range, Calory, Fat, ... ., and show them in columns B to F per date. How can I do that?

Answer 1

Food List is your lookup table, Diary has the daily totals by category (eg. cals, fat, carbs, etc.), but you are missing a Food Eaten table that logs each food item consumed and their weight.

For example, Items Eaten would need to include at a minimum Date, Food, Portion, but it could also include additional fields such as Time, Notes, Meal, etc.

Food Eaten is the data that Diary will total up using the lookup table in Food List.

Formula

You could use a formula like the following in Diary!A1 to calculate the daily totals:

={"Date", 'Food List'!B1:F1;
  SORT(
    QUERY(
      QUERY(
        BYROW('Food Eaten'!A2:C, LAMBDA(r, 
          {INDEX(r,,1), 
           IFNA(VLOOKUP(
             INDEX(r,,2), 'Food List'!A:F, {2,3,4,5,6})*INDEX(r,,3)/100)})),
        "Select Col1, SUM(Col2), SUM(Col3), SUM(Col4), SUM(Col5), SUM(Col6)
         Where Col1 is not Null Group by Col1",), "Offset 1",))}

BYROW  IFNA  INDEX  LAMBDA  QUERY  SORT  SUM  VLOOKUP

Explanation

1. The core of the formula is a BYROW function that passes the range 'Food  Eaten'!A2:C, row by row, into a LAMBDA function that stores the current row in r.
2. For each r, the LAMBDA's formula returns a single row array where,
   1. Col1 is the value from Col1 of r, either a date or an empty value, returned using INDEX.
   2. VLOOKUP is not natively an array function; however, it behaves as one when nested within one (SORT in this case). It returns a 5-column array populated with the values from columns {2,3,4,5,6} of the row in Food List that matches the food in Col 2 of r, or empty values if not matched.
   3. A new array is returned by multiplying each value in VLOOKUP's array by Col3 of r divided by 100 (per 100g). Again, returning an array is possible only because this calculation is nested within an array function (SORT).
3. Once the last r has been processed by the LAMBDA, the result is a multi-row array containing populated and empty rows.
4. QUERY is used to return the SUM of each of Cols2-6, aggregated by date (Col1), including only rows where Col1 is not Null/blank.
5. The QUERY's result is nested in another QUERY that uses Offset 1 to drop the row of labels created by the previous aggregation.
6. This array is nested in SORT with no arguments which defaults to Col1 ascending.
7. Lastly, a label row is added that uses the string "date" for Col1, and the labels from Cols2-6 of Food List for the remaining columns.

Sample Sheets Data

'Diary'

Formula in A1

	A	B	C	D	E	F
1	Date [formula here]	Calories	Fat g	Protein g	Carbs g	Salt mg
2	2024 October 05	717	1	21	160	208
3	2024 October 06	408	30	34	1	688

'Food Eaten'

	A	B	C
1	Date	Food	Portion g
2	2024 October 05	Tomato	120
3	2024 October 05	Rice	175
4	2024 October 05	Dark Rye	32
5	2024 October 06	Egg	50
6	2024 October 06	Tuna	75
7	2024 October 06	Cheddar	67

'Food List' _{^{(per 100 g)}}

	A	B	C	D	E	F
1	Name	Calories	Fat g	Protein g	Carbs g	Salt mg
2	Cheddar	406	34	20	1	644
3	Dark Rye	259	3	8	48	603
4	Egg	143	13	13	1	142
5	Rice	350	-	10	80	5
6	Tomato	18	-	1	4	5
7	Tuna	86	1	19	-	247 mg