Using Sumifs, where a range can match multiple criteria-CodePudding

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.

CodePudding user response：

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