I am using google sheets
How to use the filter function to filter date bounded between two ranges and sum horizontally these values, see attached file.
I am using google sheets
How to use the filter function to filter date bounded between two ranges and sum horizontally these values, see attached file.
Formulas
DateFormula:Please Login or Register to view this content.
ValueFormula:Please Login or Register to view this content.
SumFormula:Please Login or Register to view this content.
How aboutFormula:Please Login or Register to view this content.
Power Query
start datePlease Login or Register to view this content.
resultPlease Login or Register to view this content.
Try =SUMPRODUCT((B7:J7>=D6)*(B7:J7<=G6)*B8:J12). This will sum all cells within the date range.
To sum each row individually use =SUMPRODUCT((B7:J7>=D6)*(B7:J7<=G6)*B8:B12)
To get the array of filtered values:
=TRANSPOSE(QUERY({TRANSPOSE(A7:H12)},"select * where Col1 > date '"&text(B6-1,"yyyy-MM-dd")&"' and Col1 < date '"&text(E6+1,"yyyy-MM-dd")&"'",0))
To get the sum of each row:
={"sum";BYROW(M8:R12,LAMBDA(rowvals,SUM(rowvals)))}
Sample file linked here:
https://docs.google.com/spreadsheets...gid=2100307022
As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.
And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks