Replacing SUMIFs with SUMPRODUCT

[bark01]

Hi,

I have a SUMIFs statement that works but as it pulls data form an external sheet, erros out if I don't have both sheets open when I update.

I rarely use sumprodcuts but followed a guide to convert it and I get a value error. This is my original SumIf and resulting SumProduct. CAn anyone see what I'm doing wrong?

SUMIFS('[Cancellations Dashboard Report.xlsb]MainDataTable'!$Q:$Q,'[Cancellations Dashboard Report.xlsb]MainDataTable'!$D:$D,"A3S",'[Cancellations Dashboard Report.xlsb]MainDataTable'!$G:$G,"SMART",'[Cancellations Dashboard Report.xlsb]MainDataTable'!$A:$A,H$2,'[Cancellations Dashboard Report.xlsb]MainDataTable'!$B:$B,"MDS")

SUMPRODUCT(('[Cancellations Dashboard Report.xlsb]MainDataTable'!$D:$D="A3S")*('[Cancellations Dashboard Report.xlsb]MainDataTable'!$G:$G="SMART")*('[Cancellations Dashboard Report.xlsb]MainDataTable'!$A:$A=H$2)*('[Cancellations Dashboard Report.xlsb]MainDataTable'!$B:$B="MDS")*('[Cancellations Dashboard Report.xlsb]MainDataTable'!$Q:$Q))


Thanks

 

[steve the fish]

Do you have headers in MainDataTable?

 

[FormR]

Hi, try like this instead.

SUMPRODUCT(--('[Cancellations Dashboard Report.xlsb]MainDataTable'!$D:$D="A3S"),--('[Cancellations Dashboard Report.xlsb]MainDataTable'!$G:$G="SMART"),--('[Cancellations Dashboard Report.xlsb]MainDataTable'!$A:$A=H$2),--('[Cancellations Dashboard Report.xlsb]MainDataTable'!$B:$B="MDS"),'[Cancellations Dashboard Report.xlsb]MainDataTable'!$Q:$Q)

Note: for efficiency reasons, you shouldn't use full column references - better to use a sensible maximum row number.

 

[bark01]

Do you have headers in MainDataTable?

Yes I was just wondering myself if this was the issue.

 

[bark01]

Hi, try like this instead.

SUMPRODUCT(--('[Cancellations Dashboard Report.xlsb]MainDataTable'!$D:$D="A3S"),--('[Cancellations Dashboard Report.xlsb]MainDataTable'!$G:$G="SMART"),--('[Cancellations Dashboard Report.xlsb]MainDataTable'!$A:$A=H$2),--('[Cancellations Dashboard Report.xlsb]MainDataTable'!$B:$B="MDS"),'[Cancellations Dashboard Report.xlsb]MainDataTable'!$Q:$Q)

Note: for efficiency reasons, you shouldn't use full column references - better to use a sensible maximum row number.

That works thanks

 

[steve the fish]

Yes you will have to use cells that show numbers only in column Q of that sheet eg Q2:Q100.