CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulassumifs
CelestialBeing
CelestialBeing

Reputation: 37

Google Sheets ARRAYFORMULA and SUMIF combination

I recently noticed a mistake in my calculation, and I've identified the root cause of the problem: it seems that I mistakenly used the SUMIF function in Google Sheets' ARRAYFORMULA.

I have the sample spreadsheet here.

I wrote the ARRAYFORMULA function that results in column C, which I thought would be the same with the formula in column B.

The formula in column C:

=ARRAYFORMULA(SUMIF(H$3:H$6&I$3:I$6,"<="&A3:A31&">="&A3:A31,G$3:G$6))

The formula in column B:

=SUMIFS(G$3:G$6,H$3:H$6,"<="&A3,I$3:I$6,">="&A3)

In essence, I want to get the value for each date based on predefined values with their own periods.

Upvotes: 0

Views: 663

Answers (2)

MattKing
MattKing

Reputation: 7783

I made a new tab called MK.Help and erased all the other formulas. then i put this formula in C3:

=ARRAYFORMULA(ARRAY_CONSTRAIN(MMULT(N(A3:A>=TRANSPOSE(A3:A)),MMULT((A3:A>=TRANSPOSE(H3:H))*(A3:A<=TRANSPOSE(I3:I)),N(G3:G))),COUNTA(A3:A),1))

Does that work for you?

Upvotes: 1

user11982798
user11982798

Reputation: 1908

Please, use this formula in the cell C3:

=ARRAYFORMULA(MMULT(IF((A3:A31>=TRANSPOSE(H3:H5))*
(A3:A31<=TRANSPOSE(I3:I5))=1,TRANSPOSE(G3:G5),0),ROW(G3:G5)^0))

Upvotes: 2

Related Questions