1

My data:

A   B   C       D
D   150 hash123 2/3/2023
D   200 hash999 1/3/2023
D   200 hash999 1/3/2023
E   250 hash321 1/2/2023
E   265 hash954 1/2/2023
D   344 hash004 9/5/2022
B   434 hash075 8/5/2022
A   975 hash749 5/5/2022
C   513 hash304 1/5/2022
B   454 hash926 2/5/2021

For only the unique values in column C, I want to SUM the associated value of the same row in column B, if the value in column A is "D" and the date in column D is within the last 3 months.

So in the above that would be: 150 + 200 = 350.

I was working with COUNTIFS =COUNTIFS(D:D,">="&EDATE(TODAY(),-3),$A:$A,"D"), but I think I need to combine it somehow with a =query(unique(C:C),...) but I don't know if and how.

I already checked here, but that just counts the total unique values. And here, but that does not use unique.

Improve this question

1 Answer 1

Reset to default
2

Use sortn() and query(), like this:

=query( 
  sortn(A1:D, 9^9, 2, C1:C, true), 
  "select sum(Col2) 
   where Col1 = 'D' 
   and Col4 > date " & text(edate(today(), -3), "'yyyy-MM-dd'") & " 
   label sum(Col2) '' ", 
   0 
)

See sortn() and query().

Improve this answer
2
  • Thank you. This is only a small subset of my data though, the actual data is thousands of rows and dozens of columns (where the columns shown here are not next to each other). Would that change your solution? I'm asking in particular because of the A1:D10 and 9x9 parts in your solution that in my scenario would have to apply to a far larger set. And secondly, why are you using text for the date range filter? Thanks!
    – Adam
    Mar 2 at 21:26
  • 2
    Use an open-ended reference like A2:D to cover all the rows. The expression 9^9 gets the number 387,420,489 which is much larger than the number of rows in any spreadsheet you can create in Google Sheets. The text() function is required in order to get the ISO8601 date format required in date literals by the query() function.
    – doubleunary
    Mar 2 at 22:45

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.