CODEWITHSUNDEEP
excel-formulaexcel-2010
simien
simien

Reputation: 81

SUMPRODUCT in Excel using cell references

I have this rather inelegant excel 2010 formula for counting the number of instances that occur within a certain range (of 1 to 5 days in this case), but it works: 

=SUMPRODUCT(1*('Defect List'!$D$2:$D$999="Closed")*
(NETWORKDAYS('Defect List'!$C$2:$C$999+0,'Defect List'!$J$2:$J$999+0)>=1)) 
- SUMPRODUCT(1*
(NETWORKDAYS('Defect List'!$C$2:$C$999+0,'Defect List'!$J$2:$J$999+0)>5)
)

Q: Now I want to parameterise it by replacing the 1 and the 5 with cells M3 and N3. But I can't seem to get the syntax right - can anyone help please?

Upvotes: 0

Views: 410

Answers (1)

DTS
DTS

Reputation: 423

The * in your formula will be throwing the error, replace it with a comma so it looks like this:

=SUMPRODUCT(1*('Defect List'!$D$2:$D$999="Closed"),
            1*(NETWORKDAYS('Defect List'!$C$2:$C$999+0,
                           'Defect List'!$J$2:$J$999+0)>=M3),
            1*(NETWORKDAYS('Defect List'!$C$2:$C$999+0,
                           'Defect List'!$J$2:$J$999+0)<=N3))

You can do it all in one sumproduct, but a COUNTIFS would be a better solution, if you put the NETWORKDAYS into a column in Defect List, say AZ:

=COUNTIFS('Defect List'!$D$2:$D$999,"Closed",
          'Defect List'!$AZ$2:$AZ$999,">="&$M$3,
          'Defect List'!$AZ$2:$AZ$999,"<="&$N$3)

This means excel won't have to recalculate each NETWORKDAYS for every cell.

Upvotes: 2

Related Questions