Google Sheets SUMIFS comparing one column to another-CodePudding

I am wondering if there is a way to use column comparisons in a SUMIF or SUMIFS function or the DSUM function even. Specifically, I want to compare one value in a row with another value in the same row of data.

For example, let's say I have the following table of data:

Date 1 | Date 2 | Money Earned
1/2/22 | 1/5/22 | $23.00
1/5/22 | 1/3/22 | $11.00

I want to write a formula that sums the values in the 3rd column if Date 1 is last week and if Date 2 is later than or equal to Date 1.

I have completed a workaround solution with 2 hidden columns as follows:
Last week --> =IF(EQ(WEEKNUM(TODAY()) - WEEKNUM(A2), 1), "YES", "NO")
Date 2 After Date 1 --> =IF(B2>=A2, "YES", "NO")

Then I use:
=SUMIFS(C2:C3,D2:D3,"YES",E2:E3,"YES")

It all works, but I am trying to learn to do this a cleaner way without hidden columns that someone might move or delete.

All the examples I can find of SUM or SUMIF rely on the criteria being compared to a singular value that is either hardcoded in the formula or the value of a single cell. I want to compare data from column 1 to column 2 directly for each row. Is this possible with these formulas or any formula in Google Sheets?

CodePudding user response：

In order to check if a date was "last week", you need to consider that the current week might be the first one of the year. So, instead of comparing the WEEKNUM of today with the WEEKNUM of the other date, just compare it to the WEEKNUM of 7 days ago.

Secondly, instead of using "YES" or "NO", you could have used TRUE/FALSE values and remove the need to write IF statements, like this:

last week: =(WEEKNUM(TODAY()-7)=WEEKNUM(A2))
date 2 after date 1: B2>=A2

From there, while it is possible to build an arrayformula for SUMIFS or QUERY, it is easier to just filter the table and sum the results from column C:

=SUM(FILTER(C2:C,WEEKNUM(TODAY()-7)=WEEKNUM(A2:A),B2:B>A2:A))