How to get Sumproduct to ignore a criteria if it is blank?

Hi,

you could try an array SUM (confirmed with control+shift+enter)

=SUM(IF(ISBLANK($C16),1,$C$43:$C$211=$C$16)*(Sheet1!$A$43:$A$211=$A16)*(Sheet1!$B$43:$B$211=$B16)*Sheet1!$P$43:$R$211)

but there are for sure better and more elegant solutions.

Regards

Without knowing the dataset up perhaps:

=SUMPRODUCT((Sheet1!$A$43:$A$211=$A16)*(Sheet1!$B$43:$B$211=$B16)*(LEFT(0&Sheet1!$C$43:$C$22,LEN(0&$C16))=0&$C16)*Sheet1!$P$43:$R$211)

or, using a standard OR construct:

=SUMPRODUCT((Sheet1!$A$43:$A$211=$A16)*(Sheet1!$B$43:$B$211=$B16)*(($C16="")+(Sheet1!$C$43:$C$22=$C16))*(Sheet1!$P$43:$R$211))

note: with the latter you are open to double counting - best to use a sign based test around ORs (unless explicit requirement to do otherwise), so

=SUMPRODUCT((Sheet1!$A$43:$A$211=$A16)*(Sheet1!$B$43:$B$211=$B16)*((($C16="")+(Sheet1!$C$43:$C$22=$C16))>0)*(Sheet1!$P$43:$R$211))