Search code examples
google-sheets

How to sum an array formula over a dynamic range of data in Google Sheets?

I have the following data in a Google Sheet. Column A represents the name of an invoice item, column B represents the price for the invoice item and columns C onward represent the people responsible for paying for that invoice item. The rows represent the invoice items themselves.

Example:

    A,B,C,D,E,F
  + --------------------
1 | Item,Cost,Person A,Person B,Person C,Person D
2 | Food,100,X,,X,X
3 | Gas,30,X,X,X,X
4 | Water,25,X,X,,
Item Cost Person A Person B Person C Person D
Food 100 X X X
Gas 30 X X X X
Water 25 X X

I am trying to write a Google Sheets formula to compute the total a single person is responsible paying for in the whole invoice. An "X" in the person's column means they are partially responsible for that invoice item. In the example above, Person A would be responsible for 1/3 of the food, 1/4 of the gas, and 1/2 of the water, so $53.33.

For a single row (e.g. food, row 2 above), I can compute a single person's (e.g. person A, column C above) share of the cost via:

=(countif(C2,"X")/countif(C2:F2,"X"))*B2

To get person A's entire share of the invoice, I would just need to sum all of the values computed with this formula. However, I'd like to do this in a single cell using =arrayformula() or a lambda at the bottom of each person's column. I have gotten close with (referencing example above):

=sum(arrayformula(if(C2:C4="X", B2:B4/countif(C2:F2, "X"), 0)))

But the range C2:F2 in the =countif() needs to update dynamically based on which row the formula is being run on in the range.

Is there a way to do this in a single cell in Google Sheets? In the above example, I'd want to run this from cell C5 for Person A, D5 for Person B, etc.

Here is an example of the output table I am trying to build:

Item Cost Person A Person B Person C Person D
Food 100 X X X
Gas 30 X X X X
Water 25 X X
... ...
Totals 155 53.33 20 40.83 40.83
Solution

  • Try the following formula-

    =BYCOL(INDEX(--(C2:F4="X")*(MAP(B2:B4, BYROW(C2:F4,LAMBDA(x,COUNTIFS(x,"<>"))),LAMBDA(a,b,a/b)))),LAMBDA(c,SUM(c)))

    enter image description here

    If you want to add total of cost column then use HASTACK(). Try-

    =HSTACK(SUM(B2:B4), BYCOL(INDEX(--(C2:F4="X")*(MAP(B2:B4, BYROW(C2:F4,LAMBDA(x,COUNTIFS(x,"<>"))),LAMBDA(a,b,a/b)))),LAMBDA(c,SUM(c))))

    enter image description here