CODEWITHSUNDEEP
google-sheetssumgoogle-sheets-formulaarray-formulasgoogle-sheets-query
NotAQueryQueen
NotAQueryQueen

Reputation: 1

Sum Values Meeting Criteria that Match Row Data and Dynamic Date Columns

I have scoured the forum and found nothing similar to my situation; would love some guidance.

I would like to sum values across multiple rows where the data match two criteria: row label reference and date headers from multiple tables in one sheet. Here is the data structure:

Screen Shot of Dummy Data

I would like to Add all values matching "Criteria #1" for each month so that I get:

Screen Shot of Summary Data

I have tried sumif, sum(filter), various combinations of index/match, and to no avail. I can provide more info or context if needed to get your formula suggestions.

Upvotes: 0

Views: 64

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE({
 IF((B3:B5 ="criteria #1")*(ISNUMBER(C3:E5)),  "♠"&B3:B5 &"♦"&C2:E2&"♦"&C3:E5 &"♦", );
 IF((B8:B12="criteria #1")*(ISNUMBER(C8:E12)), "♠"&B8:B12&"♦"&C7:E7&"♦"&C8:E12&"♦", )})
 ,,9^9)),,9^9), "♠")), "♦"), 
 "select Col1,sum(Col3) group by Col1 pivot Col2 label Col1'label'"))

0

Upvotes: 1

Related Questions