=countif() and =sumif() won't work when the sending workbook is closed (as
you've seen).
Another option is to use =sumproduct():
=sumproduct(--(T26
=MID('[PriceToSales_Download.xls]CS (1M) - Q'!$A$5:$A$269,5,2))
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
======
(You don't need to array enter (ctrl-shift-enter) this =sumproduct() formula.)
Bongard wrote:
Hi, I am trying to get this countif function to work with the mid
function and can't seem to get it to count more than 1 instance. Here
is my formula below. The result that should be returned is 22, and
it's returning 1. Any help would be appreciated.
=COUNT(IF(T26=MID('[PriceToSales_Download.xls]CS (1M) - Q'!$A$5:$A
$269,5,2),'[PriceToSales_Download.xls]CS (1M) - Q'!$A$5:$A$269))
Also, I looked at other posts and couldn't seem to get any of the
solutions to work with my case.
Thanks for your help,
Brian
--
Dave Peterson