alternative from Coutif to Sumproduct

I am trying to count the ammount of accounts that have the referance AS in the referance number, i need to view these stats with closed workbooks so count if dosent work, i think i need a sumproduct(-- kind of formula, can anyone help please.

Please Login or Register  to view this content.

Thanks

FBF

http://www.xldynamic.com/source/xld....T.html#classic

Good resource just for the sumproduct function.

hope it helps.

I thought I already answered this but I guess it got caught up in the board's current crapness......so here goes again. Try

=SUMPRODUCT(--ISNUMBER(FIND("AS",'[FUNDING 2007 ADAM YAXLEY NEW.xls]JAN'!$B$15:$B$500)))

Note: FIND function is case-sensitive so this won't count accounts which contain "As" or "as". If you want to include those change FIND to SEARCH

this is ok but i need to find referance numbers with AS in them not on their own, example ref # 123AS123 i need to find the AS in the middle of the numbers i tried the formula by adding **"AS"** or "**AS**" but this didnt work.

Their must be another way,

Thanks for the help so far

FBF

Originally Posted by djfatboyfats

this is ok but i need to find referance numbers with AS in them not on their own

That's what the formula does, you don't need any wildcards. FIND or SEARCH look for the defined text in a cell. If it's there (even amongst other text) the position number is returned, e.g. if A1 contains "123AS456" then =FIND("AS",A1) gives a result of 4 [because "AS" starts at character 4].

Of course you don't care where it is, just that it's there. Using ISNUMBER and SUMPRODUCT just allows you to count the number of instances where FIND generates a number (which is the same as the number of accounts containing "AS")

Did you try the original formula?

Yes i copied it over to the sheet im working on but returns the value 0, i even tried changing the case from AS to as but still it retuns 0, i looked on this persons worksheet and the referance number are definatly there in the corect column

all sorted now, i didnt remove the ** from eith side of the letters,

Thanks for your help

FBF