Formula To Count And Sum
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
ADVERTISEMENT
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
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
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
Nov 4, 2008
Question #1. Attached are the number of ballots in each precinct for the week Nov 27. Ballots are placed in bags. If each bag contains a maximum of 500 ballots per bag and 1 person can service a maximum of 25 bags, how many people were required to process all the ballots for that week? Does this formula look correct? 70.13 person =(Sum B6:L10)/500)/25
Question #2. Due to cut backs, the State is considering using a bag size that will be 20% smaller. When it does, we believe 1 person will be able to service 20% more bags. Using the same weekly sample, How many people would be needed state wide if the new bag size were implemented? I would like to develop a formula where the percentage can be adjusted.
View 2 Replies
View Related
Oct 5, 2013
Sheet 2
Sheet 3
Sheet 6
Column B
Column C
Column A
Column C
Column B
Column C
RED
50000
[Code] .......
Is it possible to put any formula on Sheet6 to the Column B & Column C which will get the correct results with given sample above?
View 4 Replies
View Related
Dec 31, 2006
I would like to have a formula in a spreadsheet of personal bills that counts down the number of days until each one is due each month.
In one column, I have a digit in each cell to represent the day of the month each month that the bill is due. Someone helped me with a formula that can count down days to a date that is a higher number than the present day of the month, but can I get a formula to count the days from, say, December (or any month) 28th to a due date of the 6th (of the next month)?
View 9 Replies
View Related
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
View Related
Apr 3, 2007
I have a formula that counts total number matched items. I need revise it so that the count is never greater than 9. I've been trying several ways - but never get the right end results. So if I have 12 items, the answer will show 9 for those rows, then the next set of rows would show a count of 3. Here is the formula that gives me the count
=SUMPRODUCT(($J$4:$J$4032=J406)*($B$4:$B$4032=B406)*($K$4:$K$4032=K406))
View 9 Replies
View Related
May 29, 2007
i'm having trouble with a counting formula that involves two columns.
i need to count the number of times that a unique value from one column occurs/appears in another column (which i'm narrowing down to each month).
in column A1 to A5
A1 - r
A2 - b
A3 - g
A4 - r
A5 - r
and in column B1 to B5
B1 - 4/10/2007
B2- 4/10/2007
B3 - 5/3/2007
B4 - 4/6/2007
B5 - 5/4/2007
i need to know how many times "r" appears within the month of April from these two columns.
i have used this formula: =COUNTIF(a1:a5,"*r*")*(((b1:b5>=DATEVALUE("4/1/2007"))*(b1:5
View 9 Replies
View Related
Oct 10, 2007
I need a formula to count and give me totals. Here's a sample of my data tab:
WG 3806 8
WG 3806 10
WL 3806 10
WG 3806 8
WG 3806 10
WG 3806 8
My summary tab would be:
WG 3806 8 3
WG 3806 10 2
WL 3806 10 1
I need a formula that will do the math for me and provide the end results (3, 2, 1).
View 9 Replies
View Related
Feb 20, 2008
Needed count formula incombination with dates. I need to count the total of names in a colum, but I need to count them for a set period.
View 9 Replies
View Related
Jul 28, 2008
I need some guidance on creating a formula that will count a total based on 96 cells with numbers.
Cells E24:G56 are blank cells at the moment until data is added.
Cells E57:G152 are previous scores
What I’m trying to achieve is the latest 96 scores, even if I add data into the blank Cell E24:G24
View 9 Replies
View Related
Aug 8, 2008
Hi I have these formulas that look in a specific column and if the argument is true then it sums the numbers in the same row but different column.
I need it to count instead of sum. These are array formulas so in order for them to currently work you must press ctrl shift enter.
{=SUM(IF('sheet1'!I7:I510>D7,'sheet1l'!G7:G510,0))}
{=SUM(IF( 'sheet1'!I7:I510>B8,IF( 'sheet1'!I7:I510
View 9 Replies
View Related
