Is there a google sheets function to count with using arrays of sums as criteria?

Question

I have been struggling for a few days with this problem. Anyone kind enough to show some interested will be highly appreciated.

I have the table shown below.

Suppose columns represent months. I would like to know up to which months' orders have been used up.

I have tried criteria with sums of demand up to that point but I cannot seem to use criteria with the sum of total demand and an array of sums of "total units ordered".

F.e. =COUNTIF(SUM($S$2:($S$2:S$2))<SUM($S$1:S$1) is not possible.

I have tried using an index-match combo but i would have to deduct the previous max sum of "total units ordered" that meets the condition up to the previous cell. Is that possible without using vba?

Thanks in advance for your interest and time spent.

question from:https://stackoverflow.com/questions/65891370/is-there-a-google-sheets-function-to-count-with-using-arrays-of-sums-as-criteria

Answer 1

You can use a standard method of getting running totals using Sumif, combined with Match:

=ArrayFormula(match(sumif(column(S1:Z1),"<="&column(S1:Z1),S1:Z1),sumif(column(S2:Z2),"<"&column(S2:Z2),S2:Z2))-1)

I put rows 3 and 4 in just as a check of my calculations and to show the results of the two Sumifs evaluations - they aren't necessary.

You may wish to specify what should happen if the demands add up to exactly 3000, for example. The above formula would actually go to the next month, so may need some refinement if that is not what you want.