Search code examples
google-sheetsgoogle-sheets-formulaarray-formulassumifs

Using Arrayformulas with Sumifs

I'm trying to build an array formula in Google Sheets to calculate the percentage a certain item will appear on a given shelf, based on the weighted odds of all the items on the shelf. E.g., if Shelf A contains items 1, 2, and 3, each with a weight of 10, then the percentage of the shelf occupied by each item would be 10 / sum(10+10+10). There are multiple shelves with multiple items containing different weights on each shelf

https://i.imgur.com/yRIF9cG.png

I've tried combining a sum with a filter within the arrayformula to sum up the weights for only the items on the item's particular shelf, but she collective percentages of all of the items on each shelf do not add up to 100

={"% of Shelf"; arrayformula(if($B$15:$B="","",$H$15:$H/sum(filter(H:H,A:A=A15))))}

Is there another approach I should be taking? I'm not super familiar with array formulas in general, but since the number of rows I'll have will be variable, I'd rather not use a simple SUMIFS without the arrayformula.

Solution

  • You can use SUMIF with an array of the shelf ID's as its criteria:

    =ArrayFormula(filter(to_percent(B2:B/sumif(A2:A,A2:A,B2:B)),A2:A<>""))

    enter image description here