Search code examples
arraysexcelexcel-formulasumifssumproduct

Getting SUMIFS to work in array with multiple criteria incl. date

I'm trying to get an automatic number of days delivered per consultant (see row F below) per month. The Renewals Timeline worksheet (pictured below) shows the number of days delivered in each week.

Renewals Timeline Worksheet

However when I try in the CSM worksheet to use the following formula to total up the number of days delivered by the person named I get "VALUE!" error.

=SUMIFS('Renewals Timeline'!$K5:$DJ51,'Renewals Timeline'!$F:$F,$C3,'Renewals Timeline'!$K4:$DJ4,">="&D$2,'Renewals Timeline'!$K4:$DJ4,"<="&E$2)

(Formula found in CSM Worksheet - pictured below)

CSM Worksheet

FYI the value found in CSM worksheet, cell D2 is 1/1/22, E2 is 1/2/22, etc.

EDIT:

It is now clear that I need to use a SUMPRODUCT function to get this to work, is anyone able to help me write one? Nothing I do will work.

Solution

  • Try the following formula in cell CSM!D2

    =SUM((DATE(YEAR('Renewals Timeline'!$K$4:$DJ$4),MONTH('Renewals Timeline'!$K$4:$DJ$4),1)=D$2)*('Renewals Timeline'!$F$5:$F$51=$C3)*('Renewals Timeline'!$K$5:$DJ$51))

    The formula consists of 3 parts that are being multiplied and the result being summed up.

    The first checks which of the dates in the 'Renewals Timeline' sheet is the same year and month as give in the CSM sheet:

    (DATE(YEAR('Renewals Timeline'!$K$4:$DJ$4),MONTH('Renewals Timeline'!$K$4:$DJ$4),1)=D$2)

    The second checks which of the names in the 'Renewals Timeline' sheet is the same as the the one in the CSM sheet:

    ('Renewals Timeline'!$F$5:$F$51=$C3)

    Since the first is a column vector and the second is a row vector, they create a matrix of 1s and 0s of the same size as the data matrix $K$5:$DJ$51 where the 1s are all the cells that are true for both criteria.

    The third part is simply the data matrix

    ('Renewals Timeline'!$K$5:$DJ$51)

    After multiplication with the matrix of elements 1 and 2, it has only the true values remaining. Then the formula is summing those values up.

    Make sure to set the $ symbol for the criteria comparison as =D$2 and =$C3 so that you can simply drag the formula over all names and months in the CSM sheet.