google sheets - Arrayformula with sumifs and 1 static criteria - Stack Overflow

admin管理员组

文章数量:1392003

Date       PRDEF55FA9E  PRDDCACBC41 PRDFEC1A0E8
2025-03-12     0            0           0
2025-03-11  20740         22740       8680
2025-03-10  17620         12060       4200
2025-03-08  16380         15580       9880

Desired outcome: I have a table StockSoldDetails with the raw data that I would like to summarize as above. For example, C2 is the sum in StockSoldDetails of Quantity where the Date is B2 and the ProductID is C1.

The row headers (Colummn DATE) are dynamically added from a tab named AgentWorkDays:

=UNIQUE(QUERY({AgentWorkDays!$M$2:$M}, " SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col1 ASC LABEL Col1 'Date' ", 0))

The Column headers are dynamically added from a tab named Products:

=TRANSPOSE(QUERY(Products!A2:I9, "SELECT Col1 WHERE Col9='Yes'"))

In the desired outcome table, I have to manually insert the formula in each cell; for example in C2:

=(IF(NOT(ISBLANK($B$2:$B)), SUMIFS(StockSoldDetails!$D$2:$D, StockSoldDetails!$J$2:$J, $B$2:$B, StockSoldDetails!$C$2:$C, C$1 ), ""))

My goal: I would like to fill the content of the table dynamically using ARRAYFORMULA so that each time a new row is added the table content is calculated and populated.

What I've tried: I wrapped the formula in C2 in a ARRAYFORMULA() and it only works 50%. It doess generate a new line each time a new date is created but the value on each line is always the same result from the date in B2, see screenshot below:

The formula in C2 to populate column C2:C is:

=ARRAYFORMULA(IF(NOT(ISBLANK($B$2:$B)), SUMIFS(StockSoldDetails!$D$2:$D, StockSoldDetails!$J$2:$J, $B$2:$B, StockSoldDetails!$C$2:$C, C$1 ), ""))

I believe the issue is that the criteria C$1 does not meet the format for ARRAYFORMULA. How can I fix this?

Date       PRDEF55FA9E  PRDDCACBC41 PRDFEC1A0E8
2025-03-12     0            0           0
2025-03-11  20740         22740       8680
2025-03-10  17620         12060       4200
2025-03-08  16380         15580       9880

Desired outcome: I have a table StockSoldDetails with the raw data that I would like to summarize as above. For example, C2 is the sum in StockSoldDetails of Quantity where the Date is B2 and the ProductID is C1.

The row headers (Colummn DATE) are dynamically added from a tab named AgentWorkDays:

=UNIQUE(QUERY({AgentWorkDays!$M$2:$M}, " SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col1 ASC LABEL Col1 'Date' ", 0))

The Column headers are dynamically added from a tab named Products:

=TRANSPOSE(QUERY(Products!A2:I9, "SELECT Col1 WHERE Col9='Yes'"))

In the desired outcome table, I have to manually insert the formula in each cell; for example in C2:

=(IF(NOT(ISBLANK($B$2:$B)), SUMIFS(StockSoldDetails!$D$2:$D, StockSoldDetails!$J$2:$J, $B$2:$B, StockSoldDetails!$C$2:$C, C$1 ), ""))

My goal: I would like to fill the content of the table dynamically using ARRAYFORMULA so that each time a new row is added the table content is calculated and populated.

What I've tried: I wrapped the formula in C2 in a ARRAYFORMULA() and it only works 50%. It doess generate a new line each time a new date is created but the value on each line is always the same result from the date in B2, see screenshot below:

The formula in C2 to populate column C2:C is:

=ARRAYFORMULA(IF(NOT(ISBLANK($B$2:$B)), SUMIFS(StockSoldDetails!$D$2:$D, StockSoldDetails!$J$2:$J, $B$2:$B, StockSoldDetails!$C$2:$C, C$1 ), ""))

I believe the issue is that the criteria C$1 does not meet the format for ARRAYFORMULA. How can I fix this?

• google-sheets
• array-formulas
• criteria
• sumifs

Share Improve this question Follow edited Mar 12 at 13:21 TheMaster 51.3k77 gold badges7373 silver badges100100 bronze badges asked Mar 12 at 9:59 user544849user544849 933 bronze badges 1

• 1 Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Mar 12 at 10:07

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Try

=ARRAYFORMULA(IF(B2:B="", , SUMIF(StockSoldDetails!J2:J & StockSoldDetails!C2:C, B2:B & C1, StockSoldDetails!D2:D))) 

本文标签： google sheetsArrayformula with sumifs and 1 static criteriaStack Overflow