# Countif - Formula Too Long

Oct 31, 2009

I need to add more arguments to a countif formula, but I have maxed out the 1024 character limit.

Is there another way of writing this formula or is there a way I can add more arguments to this formula.

Formula is:

=countif(A2:A9999,"AH")+countif(A2:A9999,"ARHC")+countif...

I have a list of the arguments, can I do something like:

=countif(A2:A9999,B2:B50)

(with the list of arguments in columns B2 to B50)

## Countif With A Long Text String?

Jun 25, 2014

I'm trying to run a countif formula in a separate worksheet searching for a long text string. The text string is long enough that I can't copy the entire string into the Ctrl F Find function.

=COUNTIF('Data Month 1'!C:C,F4)

F4 = text string put together using the values of multiple adjascent cells. Total characters are over 26.

Aug 12, 2007

This task joins a string together based on a number of characters per cell in the range.

I want to isolate one range, Col N, and add an IF condition to it.

There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....

## Formula Too Long To Sum?

Jul 3, 2013

I am using the sum function to sum the following cells and the formula is too long. How can I make this work?

'Raw Data'!B3,'Raw Data'!D3,'Raw Data'!F3,'Raw Data'!H3,'Raw Data'!J3,'Raw Data'!L3,'Raw Data'!N3,'Raw Data'!P3,'Raw Data'!R3,'Raw Data'!T3,'Raw Data'!V3,'Raw Data'!X3,'Raw Data'!Z3,'Raw Data'!AB3,'Raw Data'!AD3,'Raw Data'!AF3,'Raw Data'!AH3, 'Raw Data'!AJ3, 'Raw Data'!AL3, 'Raw Data'!AN3, 'Raw Data'!AP3, 'Raw Data'!AR3, 'Raw Data'!AT3, 'Raw Data'!AV3, 'Raw Data'!AX3, 'Raw Data'!AZ3, 'Raw Data'!BB3, 'Raw Data'!BD3, 'Raw Data'!BF3, 'Raw Data'!BH3, 'Raw Data'!BJ3, 'Raw Data'!BL3, 'Raw Data'!BN3, 'Raw Data'!BP3, 'Raw Data'!BR3, 'Raw Data'!BT3, 'Raw Data'!BV3, 'Raw Data'!BX3, 'Raw Data'!BZ3, 'Raw Data'!CB3, 'Raw Data'!CD3, 'Raw Data'!CF3, 'Raw Data'!CH3, 'Raw Data'!CJ3, 'Raw Data'!CL3, 'Raw Data'!CN3, 'Raw Data'!CP3, 'Raw Data'!CR3, 'Raw Data'!CT3, 'Raw Data'!CV3, 'Raw Data'!CX3, 'Raw Data'!CZ3,

## Long Formula Div/0

Feb 8, 2010

im not really sure what div/0's are na dhow to counter them but i understand from the error that im dividing by something that is not valid yet.

below is my formula, among others like this. i need some assistance in getting the ISERROR code in as i am struggling; ....

## Compress The Formula Is Too Long

Nov 13, 2008

how can in compress the following formula

=IF(B3="Monthly",IF(B25="Windows 2003 Std",15,IF(B25="Windows 2003 Ent",30,IF(B25="Windows 2008 Data Center ",40,IF(B25="RHEL 5","30",IF(B25="Windows 2008 Web","15",0))))))+(IF(B3="Quarterly",IF(B25="Windows 2003 Std",40,IF(B25="Windows 2003 Ent",75,IF(B25="RHEL 5","89",IF(B25="Windows 2008 Data Center ",120,IF(B25="Windows 2008 Web","45",0))))))+(IF(B3="Semi-Annually",IF(B25="Windows 2003 Std",75,IF(B25="Windows 2003 Ent",140,IF(B25="Windows 2008 Data Center ",200,IF(B25="RHEL 5","149",IF(B25="Windows 2008 Web","90",0)))))))+(IF(B3="Annually",IF(B25="Windows 2003 Std",150,IF(B25="Windows 2003 Ent",300,IF(B25="RHEL 5","299",IF(B25="Windows 2008 Data Center ",400,IF(B25="Windows 2008 Web","180",0))))))))

## Formula Too Long For One Cell

May 23, 2006

Got a formula that goes like this: =IF((L1=1),"Product 1",IF((L2=2),"Product 2",IF((L2=3),"Product 3",IF((L2=4),"Product 4".... and so on for a further 100 or so products. Obviously, this formula is too long for one cell.

## Error Message Formula Too Long

Aug 18, 2009

I need to write a formula but excel shows an error message "Formula Too Long." There are two long strings of the formula that I repeat several times. Is there any way to put these strings in other cells and reference them within the formula? Each of the strings looks very similar to this:

## Copying A Long Formula To Another Workbook?

Oct 21, 2008

I want to do is copy a formula from one workbook to another workbook that I have open - have tried just copy and paste (only get the result, no formula is copied) and have tried paste special, but there doesn't seem to be anything relevant on the list. I'm sure there is a simple way of doing it.

## Improving Formula; IF Statement Is Too Long

Dec 21, 2009

I have a formula which is currently too long and which I'm sure could be done more efficiently. That plus Excel is saying the formula is too long. I have pasted the formula below and broken it up for clarity:

---------------------------------------------------------------------------------
=IF(AND(E3="Three",I3<=100),
(I3*VLOOKUP(C3,costsnetwork,10,FALSE)/100),

IF(AND(E3="Three",I3<=250),
(100*VLOOKUP(C3,costsnetwork,10,FALSE)/100)+((I3-100)*VLOOKUP(C3,costsnetwork,11,FALSE)/100),

IF(AND(E3="Three",I3<=850),
(100*VLOOKUP(C3,costsnetwork,10,FALSE)/100)+(250*VLOOKUP(C3,costsnetwork,11,FALSE)/100)+((I3-350)*VLOOKUP(C3,costsnetwork,12,FALSE)/100),

## MAX Formula Returns Long Number

Nov 22, 2006

I'm trying to get the max value in an array that has 3 different values that are:

array(.0027, -.0001, -.0083)

...with worksheetfunction.max(). I get the correct return, however it's 2.70000007003546E-03! How can I get it to return .0027?

## Setting Long Complicated Formula To Cell Via VBA?

Jun 12, 2014

I want to set formula to cell S1 via vba.

This is the formula: =IFERROR(VLOOKUP(H3;'[VATCompanies.xlsx]1'!\$A:\$B;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!\$D:\$E;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!\$G:\$H;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!\$J:\$K;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!\$M:\$N;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!\$Q:\$R;2;0);I7))))))

## Adding Text To Formulas - Formula Too Long

Jan 29, 2010

Is there a work around to pull data from cells in excel to another tab with standard language? For example the template language will stay the same except in example A's case the last sentence "0.0" hrs will vary based what's sitting in B34.

It's stating the formula is too long. Is there too much text?

Sometimes the link would be in the middle of the text as shown in example B below with dates 00/00/00.

EXAMPLE A
For any leave of absence that is granted for more than 10 days, time off is governed under the Disability Bank Policy. Under the Disability Bank Policy there is a 10 day elimination period which must be satisfied prior to gaining access to your disability bank. During this 10-day elimination period, you are required to use your sick time for the first 6 days (48.00 hours) and vacation time for the remaining 4 days (32.00 hours). After this elimination period, we use all the time accrued in your disability bank. Currently, you have a disability bank balance of "00" hours.

EXAMPLE B
After this disability bank is exhausted, you will use all remaining and future paid time off balances from your vacation/CME/holiday banks. Because your leave begins in 00/00/00 and ends in 00/00/00 we will advance you future paid time off balance thru 00/00/00. Once those balances are exhausted the remainder of the leave will be unpaid. Per our policy, should you not return to work, you agree to pay back any time advanced to you within 30 days of your separation from the company.

## Find, Replace Function: Formula Is Too Long

Oct 8, 2007

I'm receiving this message when I attempt to use the Find, Replace function. Formula is too long". I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.

## Formula To Find Specific TEXT In One Long Statement

May 28, 2014

formula to find specific text from statement.

I manage to use formula find for 1 text in 1 statement, but in confuse how to get another text found if i have "8 text" to find. Generally, there will only be "1 text" for each statement.

Maybe in 1 statement there have only "IC" or "Staff Tagging" or Name" or "Product" or etc till 8.

## Unable To Record Error, Long Formula Macro

Nov 25, 2008

I am working on using an excel workbook as part of a roll playing game and I am trying to record a macro and then put that in a command button which uses the randbetween, multiple if statements and multiple vlookup functions. I have successfully done similar things by just recording a macro and then pasting that into the VB editor. However this time I get a unable to record macro after putting the formula in the selected cell. I have tried copy and paste and just typing the formula with the same results.

Upon executing the formula I then want it to copy and paste special- values the result.

I'm assuming I have to put the VB code in manually but when I tried to do the formula I got a syntax error.

The worksheet I am working on is named new and I've included the formula in the a text box. I would like the result to show in J12 and be triggered by pressing the cmnbutton in k12.

## Frequency Formula Takes Long Time To Calculate

Feb 12, 2010

I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.

=SUM(IF(FREQUENCY(IF(Detail!\$A\$7:\$A\$40000=C\$7,IF(Detail!\$B\$7:\$B\$40000=\$B10,IF(Detail!\$D\$7:\$D\$40000<> "",MATCH(Detail!\$D\$7:\$D\$40000,Detail!\$D\$7:\$D\$40000,0)))),ROW(Cust)-MIN(ROW(Detail!\$D\$7:\$D\$40000))+1),1))

This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.

## Formula Taking Long Time To Get Distinct Word?

Mar 15, 2012

I get distinct word with this formula i have 30,000 rows with below formula taking lot of time with dragging to all cell

{=INDEX(List,MATCH(0,COUNTIF(\$b\$1:b1,List),0))}

## Formula To Calculate Result Whilst Avoiding Errors Is Too Long!

Jan 22, 2010

I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.

I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:

## Formula To Check Range To Make Sure All Cells Are Either Blank Or 11 Characters Long

May 8, 2012

I need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:

=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")

## Remove Commas From All Cells, Search & Replace Error: Formula Is Too Long

May 15, 2007

I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.

## Adding Today Formula In A Countif Formula

Dec 19, 2012

I have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.

=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.

My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)

This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")

## Using AND/BETWEEN In A Countif Formula

May 27, 2006

my current formula is =COUNTIF('Input Page'!A2:A50000,"=Monday")

i'd like to change it to check what day is in the field and then only do the above formula if that day is within the past week.

so i need the "=Monday" section to be changed to read "(is equal to monday) and (is between today and today-6)" ...todays date will be taken from 'Input Page'!B2:B50000

## IF Or COUNTIF Formula?

Nov 5, 2008

See attached document, there are 11 cells in which will either contain Yes or No. Looking at the different combinations that there can be there can only ever be 9 out of the 11 cells being used or 10 out of 11 being used.

Also the last question (Row 25) could be filled N/A if this occurs I would like the formula not to count that. Is there a counting formula or IF formula which can be done to help me out?

## How To Overcome The "Formula Too Long" Error On Windows Excel...

Jul 9, 2009

I have worked on this really difficult long formula, but the problem is, I have been working from my Mac. And my office, which this formula was made for, is all PC. Now generally this isn't a problem, but apparently PC's have a limit on how long the formula can be. Is there anyway to overcome this? I really need this formula to work..

## Conditional Sum Wizard &quot;Formula Too Long&quot; Message

Mar 30, 2009

How do you get around this? I only have TWO criteria!

The list is A1:T1000 in size.

## Countif Formula Not Counting?

Feb 11, 2014

i have a spreadsheet that acquires data from 42 other worksheet and in a row of cells it has yes or no at the bottom of that row i have =COUNTIF(A2:A34,"YES"). But it will not count the yes' because the cells that it is trying to count all have formulas in them like =tues!A34 or similar is there a different formula i can use to still count the yes' even in a cell containing a formula?

## COUNTIF Formula Which Shows A %

Mar 5, 2009

I already have a conditional format which is working OK. I would like to put a formula in the cell so it shows a % so currently it would be 100% as there is 3 cells below showing 3 greens.

Alternativily each colour is worth a value - Green = 1, Amber = 2, Red = 3. So Cell I3 would show 3.

## Using Countif() Formula But With 2 Criteria

Jul 24, 2008

is there any way to put 2 criteria into a countif() statment?

Say A1 to A100 are filled with information

I'd like to count if things are equal to 4 and 5

I know this isn't proper syntax:

=countif(A1:A100, "4" && "5")

I'm trying to get something like that to work.

## CountIf Formula Using / Character?

Feb 11, 2012

I want to count the number of cells in column B that contain the start with the string US/IL. Here's the formula that I created, but it returns a value of 0 instead of 590.

Is there something I need to do differently when a cell value contains the / character in the string?

=SUM(COUNTIF(B5:B1830,"US/IL"))

Once I have the correct formula for the above, I want to write another formula on the row below that counts how many rows of this 590 value have a corresponding "Yes" value in Column M.

The first formula will sit in cell B1840

The second formula will sit in cell B1841