CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Vadim 123
Vadim 123

Reputation: 3

Sum based on multiple row + header criteria

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

Answers (2)

ADW
ADW

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.

Reference:

Upvotes: 1

player0
player0

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))

enter image description here

Upvotes: 0

Related Questions