#### Formula assistance please . .

```Hi people,
Had a query passed to me about a formula and despite my efforts, have
reached the decision to seek help from your good selves!
Worksheet looks like this;

Col F                        Col AB                          Col AC
8564848485858565        5236523652365236                 0
8484848484848484        7412741274127412                 0
1234567891234567        8484848484848484                 1
2582582582582586        8484848456789253                 0
8484848484856547        8567458698562311                 0

Column F is an ever-increasing year-to-date list of 16 digit codes.
Column AB is the latest weeks list of codes that our clerk pastes in.
Column AC is our duplicate checker; current formula is
=IF(AB14="","0",COUNTIF(F:F,AB14))

I am now wanting to slightly amend the formula so that it checks against the
last 12 digits only of the numbers in Col F.
Hope you can help.
Thanks.
```
 0
Utf
4/19/2010 8:55:01 AM
excel.worksheet.functions 4936 articles. 2 followers.

3 Replies
671 Views

Similar Articles

[PageSpeed] 24

```Hi,

Try this

=SUMPRODUCT(--(RIGHT(\$F\$1:\$F\$6,12)=RIGHT(AB3,12)))

Note you can only use full columns for the range in Excel 2007
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"J.Scargill" wrote:

> Hi people,
> Had a query passed to me about a formula and despite my efforts, have
> reached the decision to seek help from your good selves!
> Worksheet looks like this;
>
>            Col F                        Col AB                          Col AC
> 8564848485858565        5236523652365236                 0
> 8484848484848484        7412741274127412                 0
> 1234567891234567        8484848484848484                 1
> 2582582582582586        8484848456789253                 0
> 8484848484856547        8567458698562311                 0
>
> Column F is an ever-increasing year-to-date list of 16 digit codes.
> Column AB is the latest weeks list of codes that our clerk pastes in.
> Column AC is our duplicate checker; current formula is
> =IF(AB14="","0",COUNTIF(F:F,AB14))
>
> I am now wanting to slightly amend the formula so that it checks against the
> last 12 digits only of the numbers in Col F.
> Hope you can help.
> Thanks.
```
 0
Utf
4/19/2010 9:24:02 AM
```Fabulous Mike, thankyou very much.

"Mike H" wrote:

> Hi,
>
> Try this
>
> =SUMPRODUCT(--(RIGHT(\$F\$1:\$F\$6,12)=RIGHT(AB3,12)))
>
> Note you can only use full columns for the range in Excel 2007
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "J.Scargill" wrote:
>
> > Hi people,
> > Had a query passed to me about a formula and despite my efforts, have
> > reached the decision to seek help from your good selves!
> > Worksheet looks like this;
> >
> >            Col F                        Col AB                          Col AC
> > 8564848485858565        5236523652365236                 0
> > 8484848484848484        7412741274127412                 0
> > 1234567891234567        8484848484848484                 1
> > 2582582582582586        8484848456789253                 0
> > 8484848484856547        8567458698562311                 0
> >
> > Column F is an ever-increasing year-to-date list of 16 digit codes.
> > Column AB is the latest weeks list of codes that our clerk pastes in.
> > Column AC is our duplicate checker; current formula is
> > =IF(AB14="","0",COUNTIF(F:F,AB14))
> >
> > I am now wanting to slightly amend the formula so that it checks against the
> > last 12 digits only of the numbers in Col F.
> > Hope you can help.
> > Thanks.
```
 0
Utf
4/19/2010 9:54:01 AM
```your welcome
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"J.Scargill" wrote:

> Fabulous Mike, thankyou very much.
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Try this
> >
> > =SUMPRODUCT(--(RIGHT(\$F\$1:\$F\$6,12)=RIGHT(AB3,12)))
> >
> > Note you can only use full columns for the range in Excel 2007
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "J.Scargill" wrote:
> >
> > > Hi people,
> > > Had a query passed to me about a formula and despite my efforts, have
> > > reached the decision to seek help from your good selves!
> > > Worksheet looks like this;
> > >
> > >            Col F                        Col AB                          Col AC
> > > 8564848485858565        5236523652365236                 0
> > > 8484848484848484        7412741274127412                 0
> > > 1234567891234567        8484848484848484                 1
> > > 2582582582582586        8484848456789253                 0
> > > 8484848484856547        8567458698562311                 0
> > >
> > > Column F is an ever-increasing year-to-date list of 16 digit codes.
> > > Column AB is the latest weeks list of codes that our clerk pastes in.
> > > Column AC is our duplicate checker; current formula is
> > > =IF(AB14="","0",COUNTIF(F:F,AB14))
> > >
> > > I am now wanting to slightly amend the formula so that it checks against the
> > > last 12 digits only of the numbers in Col F.
> > > Hope you can help.
> > > Thanks.
```
 0
Utf
4/19/2010 9:59:01 AM

Similar Artilces:

No Form Assistant in Quote Product Screen
While entering data in the QuoteProduct screen, no Form Assistant is available. Is it the usual feature or I need to configure something to view the Form Assistant? Also is it possible to see contact hierarchy in an account in the form of a Organisational Chart? Is their any add-on available in relation to this? ...

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Combining cells to create a formula
Hi, I have two cells that I want to combine to have a working formula B1 = sum B2 = d3 b3 = =b1&"("&b2&")" D3 = 10 The result in b3 is : sum(d3) How do I get this to result in the actual value in D3. I know I can simply write =D3, but the actual reason for combining the two cells is more complicated than explained here. Thanks Hi I think you are perhaps looking for the Indirect function =INDIRECT("D"&ROW(3:3)) would return the value contained in D3 As you copy down, the formula would alter to 4, 5 etc. represent D4, D5 etc. -- Regards R...

in excel how can we put formula to convert numericalfigureto word
for example : in excel i have mention 25000.00 in numerical amount , i want to know how can i convert in next colum , about word ?/; How can i put formula to make the numerical in to words like 25000 in numerical to twenty five thousands in word. There is no direct functions to convert this. For a VBA solution check out the below links http://support.microsoft.com/kb/213360 http://www.ozgrid.com/VBA/ValueToWords.htm http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "excel" wrote: > for example : > > in excel i hav...

Formula Question #18
I have built a workbook in which I have inserted a formula to tell me whether the contents of a supply bin needs replenishment or not. The formula I used is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that ...

Move/Copy A Row Based on Formulas to a New Worksheet
I want to move several rows of sub-totals (averages within sub-groups) to a summary worksheet, but I get the Ref error. How can I copy sub-group averages to another worksheet? Thank you. high light and copy. select where you want it. edit>paste special>values. this will turn you formulas into hard numbers. you are getting the #Ref error because on the other sheet where you pasted the formulas, the formula no longer had the same references that they had on the other sheet. for example: =sum(a1:a10) in cell a11 you copy and paste on another sheet at cell a1. excell tries to compensat...

Erase data, preserve formula's
I have a an excel file with 12 worksheets for the financial year and an additional worksheet for yearly totals. I need to get a blank copy of this and was wondering if anyone knew a way to delete all the user inputted data while keeping the formatting and formula's intact. Any help is much appreciated. -- urbanfox ------------------------------------------------------------------------ urbanfox's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22826 View this thread: http://www.excelforum.com/showthread.php?threadid=519004 Hi Hit F5 and select Special a...

How do I set up a formula in excel that is the tenth root of 7 ve.
How do I set up a formula in excel that is the tenth root of 7 versus the square root of 7? try the formula =7^(1/10). HTH Alan P. "kestig1" <kestig1@discussions.microsoft.com> wrote in message news:99C16AB7-8BD6-4671-B138-5F2961E46067@microsoft.com... > How do I set up a formula in excel that is the tenth root of 7 versus the > square root of 7? ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

When launching Outlook I recieve the following error... please he
When launching Outlook 2003 I recieve the following error: The add-in "C:\PROGRAM~1\SPAMBL~1\Bin\461~1.0\SBOLExt.dll" could not be installed or loaded. This problem may be resolved by using Detect and Repair on the Help menu. I ran the Detect and Repair and that didn't work. I ran the repair option from Add and Remove Program in Control Panel and that didn't work. I also un-installed and then re-installed Outlook 2003 and that didn't work. I'm running Windows 2000. Any ideals...? Did you have something possibly called SpamBlocker installed at some point?...

I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

formula: counting presence
Hello! I have to count presence of employees from sheets between START and END, which is stored in G9 cell. I think it should be something like: =SUM(IF(START:END!G9="present"; 1; 0)), but this one returns #REF and I don't don't why. Try these from a post of mine today. One way. Put the sumif on each sheet with an indirect reference to d12 of the master. then use =sum(sheet1:sheet21!a2) where a2 in your sumif formula. One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select all>type the formula in the cell desired>after the error msg>delete from the ...

Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Office 2010 Buying Question Assistance Needed
I've been looking through the MS Office 2010 web site to try to determine what my new small company would require, but I can't find the information I need. We for sure would need Office Pro Plus, but other than that I'm not sure. We want to run it on our own server. We will initially have 3-5 people using it and perhaps more later on. Would we need to purchase site licensing? Unfortunately, our programmers are MS haters (I'm not) and I can't get any assistance from them on this, but I have power of the pen. I would appreciate any assistance I can get. Th...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

Copy/Paste formulas
How do I keep certain cells (those I want to point to a specific 'constant') from incrementing while the remaing cells in my formulas increment as expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains a set value that I want to be placed in the result as I step down the incremental (I) rows when the character "V" is found in the particular (I) row. When I do my copy and paste, the (E) row increments as the (I) rows increment. \$E\$65 "BobG" wrote: > How do I keep certain cells (those I want to point to a specific...

HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE
My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 and e1 the cells d1 and e1 have the following formula : d1=c1-b1 e1=d1-c1 but i want the following d1 should be b2-a2 and e2 should be b3-a3 how do i do this? One way... In C1 enter & copy across: =INDEX(\$A:\$A,COLUMN()-COLUMN(\$C\$1)+1)-INDEX(\$B:\$B,COLUMN()-COLUMN(\$C\$1)+1) anantth wrote: > My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 > and e1 the cells d1 and e1 have the following formula : > d1=c1-b1 > e1=d1-c1 > > but i want the following > d1 should be b2-...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! \$D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!\$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

stop automatically changing formula!
i have a countif function COUNTIF(Locking!I16:I40,"f") when i copy this and paste it to the next cell, the formula automatically change to COUNTIF(Locking!J16:J40,"f") How do I stop it from changing column I to J?!?!?! thanks. Caryn, =COUNTIF(Locking!\$I\$16:\$I\$40,"f") or =COUNTIF(Locking!\$I16:\$I40,"f") HTH, Bernie MS Excel MVP "caryn" <caryn.tan@gmail.com> wrote in message news:d7n4u3\$hgi\$1@avnika.corp.mot.com... > i have a countif function > COUNTIF(Locking!I16:I40,"f") > when i copy this and paste it to the nex...

Formula for competition timesheet
Here is the situation. I have a number of members in a clay target club who shoot a competition over a number of ranges. Ranges 1 to 8. They shoot a competition over 4 days. They start shooting at a specific time each day. Start time in cell A1. The duration of the time they spend on each range is specified in B1. These times may vary each day. I have set up a table in the worksheet that shows the squad numbers in column A, the ranges they shoot each day and the time they start to shoot on each range. This table only shows the squad numbers up to the number of members shooting, which is ...

Formulas in Excel using [R-1] not A10 references
I converted a Word document into Excel 2003 and when creating formulas,they are formatted as the cell the formula is in plus or minus a number of rows or columns. An example: =SUM (R[-4]C:R[-1]C). I would like to see these formulas in the regular format referencing Row and Column number i.e. =Sum A1:V52 Tools >Options, the General tab, uncheck R1C1 reference style "Drregion" wrote: > I converted a Word document into Excel 2003 and when creating formulas,they > are formatted as the cell the formula is in plus or minus a number of rows or > columns. An example...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

PivotTable Formulas
I have set up a pivot table. I have been asked to add a column at the end of the pivot table that calculated the %variance of sales from this year versus last year. I tried to create the formula (Calculate Field) after I had grouped my dates for Months & Years. I had to ungroup these to be able to create the formula. I do not see how when I have ungrouped the date to distinguish between 2003 & 2002 in my Pivot formula. Is this possible of am I flogging a dead horse here? Any suggestions would be most help ful Regards GarethG ------------------------------------------------ ~~...