Search code examples
google-sheetsgoogle-sheets-formulacriteria

Using Sumifs, where a range can match multiple criteria

I have 2 sheets in Google Sheets:

  • All contacts w. week/month/year, where we pull all of our deals for the year
  • Weekly Marketing analytics, where we match up all of this information on a weekly basis

In the Weekly Marketing analytics, every column from B2 to BB2 is a week number (1-53), and A1 is the current year (2021 with this sheet).

I am currently trying to find and sum, every deal value on a 10 week running average (week 1-10, 2-11 etc.) and in order to do that, I've created this formula:

=SUMIFS('All contacts w. week/month/year'!$D:$D;'All contacts w. week/month/year'!$T:$T;$A$1;'All contacts w. week/month/year'!$Q:$Q;C$2:L$2; 'All contacts w. week/month/year'!$E:$E; "ORGANIC_SEARCH")
  • 'All contacts w. week/month/year'!$D:$D - This is the column with all of the revenue for a given deal.
  • 'All contacts w. week/month/year'!$T:$T;$A$1 - This is the column with the year of the deal.
  • 'All contacts w. week/month/year'!$Q:$Q;B$2:K$2 - This is the column I am having trouble with. This is the column with the week number of the deal, and since I am doing a 10 week running average, I want every deal for the last 10 weeks. So all deals from week 1, 2, 3 etc that matches all of the other criteria.
  • 'All contacts w. week/month/year'!$E:$E; "ORGANIC_SEARCH" - This is the last criteria, defining which marketing channel the deal originated from.

Currently this formula returns 0, despite there being a deal in week 4, and I cant work out why the system is missing it?

In the bigger picture, this is the for I want to end out with:

=IF(SUM(C$116:L$116)>0;SUM(SUM(C$143:L$143)/SUMIFS('All contacts w. week/month/year'!$D:$D;'All contacts w. week/month/year'!$T:$T;$A$1;'All contacts w. week/month/year'!$Q:$Q;C$2:L$2; 'All contacts w. week/month/year'!$E:$E; "ORGANIC_SEARCH")); 0)

Quickly checking if there is any deals counted in those weeks, if yes then sum up the marketing cost for a given channel, divided with the revenue of the deals for those given weeks.

Solution

  • It's hard to say without seeing the actual data, but it looks like the Criteria2 argument is problematic, because C$2:L$2 is really an array of criteria. So depending on your version of Excel, it would return either a spilled array or the first element of the array (basically only using C$2 as a criteria.)

    If the week numbers are numeric (1,2,3 etc.) instead of text (Week1, Week2, Week3, etc.) then you ought to be able to break it out into 2 criteria using inequalities. For example, suppose L2 is week 11. That means the expression L2-9 provides your lower bound of 2. So in your SUMIFS function you should sum everything greater than or equal to L2-9 AND less than or equal to L2.

    Your formula might look something like this:

    =SUMIFS('All contacts w. week/month/year'!$D:$D;'All contacts w. week/month/year'!$T:$T;$A$1;'All contacts w. week/month/year'!$Q:$Q;">=" & L2-9; 'All contacts w. week/month/year'!$Q:$Q; "<=" & L2; 'All contacts w. week/month/year'!$E:$E; "ORGANIC_SEARCH")