Two Special Count Formula
Feb 5, 2007
two formulas to search a range containing a unique 9 digit number which contain duplicates and then see if there are one of four values in another column.
Column A contains a unique SBI number(9 digits) and will be duplicated. In column E there are a possiblility of 4 other values which are read off a list. These are "Open", "In Progress", "On Hold" and "Closed.
SBI's Open
Need this formula to everytime it finds a unique SBI it goes to column E and count if any of the duplicate SBI's have Open", "In Progress", "On Hold" task and count it as an SBI which is still OPEN.
SBI's Closed
Need this formula to everytime it finds a SBI it goes to column E and count if any of the duplicate SBI's have only "Closed" tasks and count that.
There are duplicate SBI's because there are "Tasks which are listed under multiple SBI, so an SBI number can have 1 to ? tasks under it.
Want to basically count that all tasks under that SBI are closed if they are count it if not it's still open.
Have a formula to count the individual SBI already so maybe a only have the SBI closed formula and minus that count against the total SBI's to give the not closed SBI's ?
This formula is
=SUM(IF(A7:A2732="","",1/COUNTIF(A7:A2732,A7:A2732)))
If someone can tell me how without using a Pivot table as I have 260 spreadsheets which need this data and the pivot table takes too much memory.
View 9 Replies
ADVERTISEMENT
Jun 26, 2013
I use the following formula to count unique values within a group for a pivot:
=IF(SUMPRODUCT(($CO$2:$CO55681=CO55681)*($E$2:$E55681=E55681))>1,0,1)
When I paste values over the formula it takes around 1 hours to do the 70k cells which is excessive. I want to know if there is a way to speed it up?
I tried doing an =cell and then paste special that and it works really fast but when I delete the actual formula's again the whole sheet locks up.
View 9 Replies
View Related
Mar 10, 2009
I've got a spreadsheet with over 60,000 entries each with a different post code. I want to filter and then count to find the most re-occuring post code. However, I want to do this just on postal area, so I need to edit the info so the last 3 characters of the post code are removed, eg...
SE10 4BX
becomes
SE10
other than doing it manually is there anyway to just command excel to delete last 3 characters from my list?
View 4 Replies
View Related
Jan 22, 2014
I use a sumproduct to count a special content from a table.
I have the country, a special key and the product.
I have different products like rismo 12, rismo 100, rismo 200.
But i want to count them all.
So i have the following function.
=+SUMPRODUCT(((ISNUMBER(FIND("rismo";E1:E1000))*1)*(C1:C1000=D2)))*((H1:H1000=60)+(H1:H1000=65)).
In the cells c are the countrys. In the cells h are the special keys. I am looking for the 60 and 65.
My result is always 0.
View 3 Replies
View Related
Jul 22, 2014
I need to create a list of results based on a system entered on by a user with data already there. The first number they enter is the UCN (a number) then either indicate if it is a rail road or not. The values for this is Y or N. I need to have it where if they put Y then a certain formula is calculated and if they Indicate N then I have another formula calculated the results are put into the APN column. I also need to have it where I don't get an error if I have either of the cells (UNC or Railroad) ...
I uploaded a sample file of what I have so far : Book2.xlsx
View 7 Replies
View Related
May 8, 2009
I was hoping someone could help me out. I've been trying to put together a formula to find special characters with no luck.
Characters I’m looking for are:
View 11 Replies
View Related
Dec 12, 2008
I would like to use the following line to calculate the formula,
In the format i have already specified.
But to leave the values in the cells apposed to the formula.
This way when i delete the source sheets the results will still be left as values not formulas.
It can probably be done in 3 lines but i have to do this for 9 different rows so was wondering if there is a way of combining the three tasks into one row
View 14 Replies
View Related
Aug 16, 2009
I thought that a nested if is what I needed, but now I don't know what I need I have explained in greater detail within my sample that's attached. Quote:....
I don't know how to write a function for what I need. Right now I have a function that says '= if $b7=0,0,if($d7>$d5,-1,1)), but I've found a problem. If there's more than one zero it throws off what I'm doing. I need a function that will ignore any zeros within column D and use the second to last number. For example:
The formula in E12 should read '=if($B12=0,0,if($D12>$D8,-1,1))
The formula in E13 should read '=if($b13=0,0,if($D13>$D9,-1,1))
The formula in E20 should read '=if($b20=0,0 ,if($d20>$d12,-1,1))
I just don't know how to write this so that it will go back to the second to last number other than zero.
View 3 Replies
View Related
Jan 10, 2014
know while doing paste special whether we can copy the data in reverse manner instead of regular pattern. Here, I would like to explain my query in detail.
As you all aware that we use paste special command for multipurpose requirements such as breaking the links/transpose etc. But, here I need to copy the data from last row figure as first in the same context first one as final.
Ex: if Data is 123456 and if we paste the data by using paste special, data will be same may be in in row /column. Here, I wanted to copy the data as 654311 which means reverse. Hence, kindly guide me whether is there any command/formula for doing the same.
View 1 Replies
View Related
Jan 20, 2008
I want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used)
(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
View 9 Replies
View Related
Aug 21, 2006
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
View 6 Replies
View Related
Mar 26, 2009
I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
View 2 Replies
View Related
Feb 5, 2010
I want to count cells in column AA that are graeter than 160, and in column N = "RM" and in column A = "CBP". Can't seem to get this right.
View 4 Replies
View Related
Oct 19, 2009
I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B
Document No Category
11000001 A
11000002 B
11000003 B
11000002 A
11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
View 9 Replies
View Related
Feb 22, 2007
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View 2 Replies
View Related
May 19, 2009
I have a file where I want to count number of cells where the value is greater than 0. in the attachment, i just want to populate that count below the column indicated therein. So in the example, desired result is two.
View 4 Replies
View Related
Jan 16, 2006
in writing a formula that will count the number of times
the store is listed (Column B) when it matches with closed (Column C).
On the table listed below I will return the data using a match.
From this table
A B C
1/8/2006 9:45Store 1Closed
1/8/2006 9:57Store 2Closed
1/8/2006 10:05Store 3Closed
1/8/2006 10:09Store 4Closed
1/8/2006 10:15Store 5Closed
1/8/2006 10:24Store 1Closed
1/8/2006 10:36Store 2In Progress
1/8/2006 10:41Store 3In Progress
1/8/2006 10:50Store 4Closed
1/8/2006 10:58Store 5Closed
1/8/2006 10:59Store 1Closed
1/8/2006 11:15Store 2Closed
1/8/2006 11:22Store 3In Progress
1/8/2006 11:24Store 4In Progress
1/8/2006 11:33Store 5Closed
1/8/2006 11:51Store 1Closed
1/8/2006 11:56Store 2Closed
1/8/2006 11:57Store 3Closed
1/8/2006 12:03Store 4Closed
1/8/2006 12:16Store 5Not Started
1/8/2006 12:23Store 1Closed
1/8/2006 12:28Store 2Closed
1/8/2006 12:57Store 3Closed
To this table
A B C
1/8/2006 9:45Store 15
1/8/2006 9:57Store 24
1/8/2006 10:05Store 33
1/8/2006 10:09Store 43
View 11 Replies
View Related
Jul 6, 2008
my destination cells will have formulas like:
='E:My WorkTestData[CCC.xlsx]Input'!$S$31
Everything works fine. But I don't want the destination cells to display the full formula, but the actual Value only
I thought of a way to build a script that first builds the formula to one cell then Copy Paste Special value of that Cell to my desired Cell.
But when the code runs The cursor moves back and forth all the time...
View 9 Replies
View Related
Jan 20, 2013
I have a spreadsheet that keeps track of document collection.
Column A is document name
Column B is department name
Column C-N represent quarters of the year. Ie 1st qtr 2012, 2nd qtr 2012 up to 4th qtr 2014
Conditional formatting changes the row to red if the last day of the qtr is less than today showing those documents as past due.
I mark the Cell "Good" if the documents received meet quality checks.
What I would like to do is:
Create a formula showing the present completion percentage by department.
The trouble I'm having is discounting the future cells that aren't applicable until they become past due.
I thought just counting the red cells and green cells but I can't get any of the conditional formatting counting codes to work for me. Tried pearson's CF vba and similar.
In one cell I can get the CFColorIndex to work and pull back the color index but in another cell trying same syntax trying to get the color index of a different cell I get #Value. CountCFColorIndex I just get #Value no matter what I try.
Can I count blank cells in a range if the Qtr ending date is less than today?
Would I have to have a multiple if formula to capture each qtr?
View 5 Replies
View Related
Mar 15, 2013
I have the following problem, let's say I have the following arrangement in Column A (seen below) I want to count the number of cells with "Metal Loss" up until the word "Group" comes up and put the count in let's say Column B Cell 1. Then I want it to continue counting Metal Loss until it reaches another Group and put the next count in Column B Cell 2.
Group
Metal Loss
Metal Loss
[Code] ......
View 5 Replies
View Related
Mar 11, 2009
I'm working with an excel file that starts off with two columns and 1300 rows. Employee ID in A and Salary dollars earned in Column B. The total in column B is, let say $60,000,000.00. This is sorted by column B, desending. I've added a third column that tells me what 5% of the $60mil is (c2), C3 = what 10% of 60mil is, C4 = What 15% is, C5 = What 20% of 60mil is...and so on to what 95% of 60mil is in C20.
What I'm trying to determine is how many employees take up or make up 5% of the total salary (60mil)?, then how many employees take up 10% of the 60 mil, and so on to 95% of the 60mil.
View 11 Replies
View Related
Oct 24, 2008
See attached file. There are 10 Cells in column G which are Yes/No. On the Calculation sheet you can see the formulas which gives you the % on Score 1 Sheet. I have one problem which I don't even know if this is possible to resolve.
In Cell G12 if it is Yes then you leave G13 blank, if No then you must fill in G13. I was wandering if you look at my current formula on the calculation sheet in cell A4 is there a way where if G12 is yes then you count count 8 cells to gain the % but if G12 is no you count 9 cells to gain the %?
View 2 Replies
View Related
Nov 21, 2008
Trying to get a total formula for these rows.....
Row T has 0 thur 9 ---on the left side the total
of rows B3 to B12 and F3 to F12 and J3toJ12--Butt
C--G--and K change twice a day.
Row T is fixed (does not move with the toal of the left...
Would a formula work for this????meaning the numbers
totaling by themselfs ---like 1 in row C has 3 and 1 in row
G has 9 and 1 in row K has 1 = 13 ---which will be putt
in row S total.
We change the rows twice a day with new results..
Butt Row T is fixed like I said before....with the total...
We have three different systems 3digit,4digit,combo
hope I explained it right.
View 4 Replies
View Related
Jan 20, 2013
I am attaching the sheet : Uniquecount.xls
Need to do this using formulas and without needing to add any new columns.
Need a formula to lookup the details sheet (for easyness i just added it as a new tab here) and count the unique names in a dept and display that in the summary sheet/tab.
Want the formula to be applied to the summary to go and lookup/count from the other sheet.
Have shown the expected results in the col "total unique heads".
View 1 Replies
View Related
Feb 27, 2013
Any formula to count only unique values in particular column
E.g.
1
2
3
1
5
1
6
1
answer should be 5
View 3 Replies
View Related
Sep 18, 2009
Im looking for a formula to count different symbols in a row. I am using =COUNTIF(A1:A1600,CHAR(251)) to count 1 symbol and =COUNTIF(A1:A1600,CHAR(252)) to count another. what i need is a formula to count all symbols no matter what they are. As in this case they will only be the 251 + 252 a formula just for that would be great.
View 4 Replies
View Related
Oct 4, 2009
I have a range of cells that may contain several different abbreviations (text groups). I have used the COUNTIF function with * place in front of text to count cells that contain the listed text eg =(COUNTIF(G6:G39,"*HWD")) which has worked well and returns an accurate count of the cells that contain HWD. I have tried to do the same thing for a COUNT function that contains an IF function and no count is returned. When I remove the * from the formula a count is returned for cells that contain only the HWD text and not cells that contain HWD and other text. {=COUNT(IF(P9:P69="MCAW",IF(G9:G69="HWD",J9:J69)))}.
View 3 Replies
View Related
Feb 11, 2010
I have a 2007 spreadsheet where a person's age is entered into a cell. I need to count how many are between 26-30, 31-35, 36-40, etc.
View 2 Replies
View Related
Feb 9, 2010
I'm trying to get the 2 formulas described down below to work but I keep getting 'Run-time error: 1004 Application or object defined error'.
With the following formula I'm trying to have the COUNT function applied to;
Row 143:147
Row 153:157
Row 163:167
Row 173:177
View 4 Replies
View Related
Nov 30, 2006
I have a column with names and I would like to count how many names there are in this columns. This is easy but in fact if there is twice the same name the formula will count it twice and I don't want that. Is there a solution to count only the number of persons present in this column without counting twice the same person??
View 12 Replies
View Related
