Reputation: 3
I have a problem with summing up my values from a data set, that's structured like this:
The goal is to sum the revenues separated by company and split by month, so the result is output in this way
I have tried it with some =sumifs + index/match and =sumproduct solutions, but can't seem to make it work.
Here's the sample file:
https://docs.google.com/spreadsheets/d/16xOoPCHDtcSRRojCkwcBorUc5dstgkXFPR6M_d5uY2U/edit#gid=0
Upvotes: 0
Views: 248
Reputation: 4257
On the "revenues" tab, in cell B4, try using the formula:
=SUMIFS(indirect(address(1,match(A4,Overview!$3:$3,0)-1,,,"Overview")&":"&address(1000,match(A4,Overview!$3:$3,0)-1)),Overview!A1:A1000,">="&B$2,Overview!A1:A1000,"<="&B$3)
To break it down, this bit helps figure out which revenue column to use by matching the name of the company and then taking the column before that:
match(A4,Overview!$3:$3,0)-1
This bit creates an address "Overview!$G$1":
address(1,match(A4,Overview!$3:$3,0)-1,,,"Overview")
This bit creates the 2nd part of the address i.e.":$G$1000":
"&":"&address(1000,match(A4,Overview!$3:$3,0)-1)
And the rest is a SUMIFS
where it sums the revenue column for dates after the 1st of the month and before the last date of the month.
Be careful: your data is for 2020 and your summary table is using dates in 2021.
Upvotes: 1
Reputation: 1
use in A4:
=ARRAYFORMULA(QUERY(QUERY({SPLIT(FLATTEN(IF(
FILTER(Overview!G7:1000, MOD(COLUMN(Overview!G7:1000)+2, 3)=0)="",,
TEXT(Overview!A7:A25, "m")&"×"&
FILTER(Overview!G7:1000, MOD(COLUMN(Overview!G7:1000)+2, 3)=0)&"×"&
FILTER(Overview!G3:3, MOD(COLUMN(Overview!G3:3)+1, 3)=0))), "×");
SEQUENCE(12), SEQUENCE(12, 2,,)},
"select Col3,sum(Col2)
where Col1 is not null
group by Col3
pivot Col1", 0),
"offset 2", 0))
Upvotes: 0