CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Randal Andrade Nunes
Randal Andrade Nunes

Reputation: 4864

Google Sheet sum and multiply numbers based on dates

I am trying to sum the packages(B) if dates are equal, then compare this sum with the Orders per Day column(C) and multiply by the Rate Per Package(D) depending where the sum falls within the Orders per Day number.

I would like to return just the total number if possible.

DEMO: https://docs.google.com/spreadsheets/d/1oSFnjogyXYybsqIYgQW2m45bbyAVBTKr3EVgxcGWFQo/edit?usp=sharing

enter image description here

Upvotes: 0

Views: 396

Answers (1)

marikamitsos
marikamitsos

Reputation: 10573

You can try the following 2 formulas

In cell F2 place

=QUERY({A2:D},"select Col1, sum(Col2) 
                 where Col1 is not null 
                 group by Col1 label sum(Col2)''",0)

Then in cell H2 use

=ArrayFormula(IFS(G2:G>=80,G2:G*D9,
                  G2:G>=59,G2:G*D8,
                  G2:G>=39,G2:G*D7,
                  G2:G>=19,G2:G*D6,
                  G2:G>=14,G2:G*D5, 
                  G2:G>=9,G2:G*D4,
                  G2:G>=4,G2:G*D3,
                  G2:G=1,G2:G*D2, 
                  G2:G="",""))

(You can adjust ranges to your needs)

enter image description here

Functions used:

Upvotes: 2

Related Questions