Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
Hopefully I explain this well. I have a sheet with 2 tabs, Data and Report. I am trying to make a formula on the Report tab that sums up total paid by new Clients.
Essentially: Look at Data!A:I, find all Clients who appear in December 2021 (aka F3) but not November 2021 (aka E3) and sum up their Column I. In the case of the above example, it'd pop out $2, the sum of I6 and I7 - since Clients C and D are new.
Create a helper column to identify new clients with this formula in cell Data!J1:
=arrayformula(
{
"New?";
iferror( 1 / (
row(C2:C) = vlookup( C2:C, { C2:C, row(C2:C) }, 2, false )
) ^ -1 )
}
)
Then use filter() in Reports!F5, like this:
=sum( iferror( filter(
Data!$I2:$I,
month(Data!$B2:$B) = month(F3),
year(Data!$B2:$B) = year(F3),
Data!$J2:$J
) ) )
The formula assumes that Report!3:3 contains dates rather than text strings that look like dates.
Try this in your report sheet:
=ArrayFormula(TRANSPOSE(QUERY(FILTER({Data!B2:B,Data!I2:I},COUNTIFS(Data!C2:C,Data!C2:C,ROW(Data!C2:C),"<="&ROW(Data!C2:C))=1),"Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1 LABEL SUM(Col2) '' FORMAT Col1 'mmm yyyy'")))
This should deliver the full report with one formula.
If you believe that you will ever have months between the earliest and latest months that have no entries listed, but you want to include those months with a zero sum in the report, then use this version:
=ArrayFormula(TRANSPOSE(QUERY({FILTER({Data!B2:B,Data!I2:I}, COUNTIFS(Data!C2:C,Data!C2:C,ROW(Data!C2:C),"<="&ROW(Data!C2:C))=1); EDATE(Data!B2,SEQUENCE(DATEDIF(MIN(Data!B2:B),MAX(Data!B2:B),"m"),1)),SEQUENCE(DATEDIF(MIN(Data!B2:B),MAX(Data!B2:B),"m"),1,0,0)},"Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1 LABEL SUM(Col2) '' FORMAT Col1 'mmm yyyy'")))
