CountIf - mid()
 

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

CountIf - mid()
 

Try it this way:

=SUM(IF(T26=MID('[PriceToSales_Download.xls]CS (1M) - Q'!$A$5:$A
$269,5,2),1,0))

Note that this is an array formula, which means that you have to
commit it using the key combination of CTRL-SHIFT-ENTER (CSE) instead
of the usual <Enter. If you do this correctly then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
you should not type these yourself. If you subsequently amend or edit
the formula you need to use CSE again.

Another way which does not need to be array-entered is:

=SUMPRODUCT(--(T26=MID('[PriceToSales_Download.xls]CS (1M) - Q'!$A$5:$A
$269))

Hope this helps.

Pete

On Mar 18, 12:33*pm, 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

CountIf - mid()
 

Hi, try
=sumproduct((MID('[PriceToSales_Download.xls]CS (1M) - Q'!$A$5:$A
$269,5,2)=t26)*('[PriceToSales_Download.xls]CS (1M) - Q'!$A$5:$A$269))

Bongard;274543 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


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=76556

CountIf - mid()
 

=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

CountIf - mid()
 

Thank you guys for your suggestions. I ended up wanting to make the
formula more dynamic so I added a named range as well. The formula
that I have working for me is the following: =SUMPRODUCT(--(MID(TEXT
(AlphaTestRange,"00"),5,2)=TEXT($T26,"00")))