I am trying to get a sheet to update counts from another workbook automatically. The countifs formula is working fine:
=COUNTIFS('G:\[shiplog.xls]Returns'!$C$1:$C$65536,">" & DATE,'G:\[shiplog.xls]Returns'!$D$1:$D$65536,A11,'G:\[shiplog.xls]Returns'!$E$1:$E$65536,B11,'G:\[shiplog.xls]Returns'!$F$1:$F$65536,C11,'G:\[shiplog.xls]Returns'!$A$1:$A$65536,">5999",'G:\[shiplog.xls]Returns'!$W$1:$W$65536,"")
What the problem is that I have to have that data workbook open to update my sheet, otherwise I get #VALUE errors. I know that I can use SumProduct but I can't get all my criteria to work.
I have this working but for some reason, it finds nothing when I add the rest of the criteria.
=SUMPRODUCT(([shiplog.xls]Returns!$C:$C>DATE)*([shiplog.xls]Returns!$D:$D=A10)*([shiplog.xls]Returns!$E:$E=B10))
Can someone assist in transposing this Countif to SumProduct?
Thanks in advance.
Mike
=COUNTIFS('G:\[shiplog.xls]Returns'!$C$1:$C$65536,">" & DATE,'G:\[shiplog.xls]Returns'!$D$1:$D$65536,A11,'G:\[shiplog.xls]Returns'!$E$1:$E$65536,B11,'G:\[shiplog.xls]Returns'!$F$1:$F$65536,C11,'G:\[shiplog.xls]Returns'!$A$1:$A$65536,">5999",'G:\[shiplog.xls]Returns'!$W$1:$W$65536,"")
What the problem is that I have to have that data workbook open to update my sheet, otherwise I get #VALUE errors. I know that I can use SumProduct but I can't get all my criteria to work.
I have this working but for some reason, it finds nothing when I add the rest of the criteria.
=SUMPRODUCT(([shiplog.xls]Returns!$C:$C>DATE)*([shiplog.xls]Returns!$D:$D=A10)*([shiplog.xls]Returns!$E:$E=B10))
Can someone assist in transposing this Countif to SumProduct?
Thanks in advance.
Mike