0

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.

enter image description here enter image description here

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.

1

2 Answers 2

1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.