Add Number Of Occurances Base On Criteria On Two Columns
Oct 3, 2009
How can I add the number of remarks to the number classes based on their row?
I have a TALLY SHEET which auto computes the number of occurances of each classes
and remarks…can someone help me how to add the class and remarks? In this example
you can see that CLASS A occurred 3x ,YES remarks occurred 2x and NO remarks occurred 1x for A class…
how can I add the occurances of YES and NO remarks to A class?
This should be the output…Remarks are being added according to the class they belong
ABEYESNONot Applicable
643242
View 13 Replies
ADVERTISEMENT
Oct 7, 2009
how to count the number of occurrences base on a criteria? My sample file contains a Tally Sheet and a template sheet...how can i count the number of occurrences of Yes and No per class? Say for the A class, how can i count the yes or no?
View 2 Replies
View Related
Apr 29, 2009
I am trying to use an SUM array to count occurances of a given criteria.
Here is an example of one that works for me.
View 3 Replies
View Related
Jun 23, 2006
I have a column that has almost 500 rows. Each cell has " DELETED", "OPEN" or "CLOSED" written in the cell. I need to know how to calculate how many times "OPEN" is listed throughout the column.
View 4 Replies
View Related
Feb 17, 2014
I would like to sum the amount column of unique date that is in active status of each account.
Account Date Status Amount
11 jan 1 A 200
12 jan 1 A 5
13 jan 1 A 1000
11 jan 1 A 200
11 jan 3 A 100
12 jan 3 A 8
13 jan 4 A 1
13 jan 4 A 1
11 jan 6 I 50
12 jan 6 A 10
Below is the expected result
Account Amount
11 300 (200+100) [200 is only add once since jan 1 of 11 has appeared more than one time]
12 23 (5+8+10)
13 1001(1000+1)
View 5 Replies
View Related
Nov 20, 2006
I have a macro that has following line
Range("C38'").Select
I want to dynamically change the C38 to may be C37 depending on a value in a cell i.e. F1
so if the value in cell F1 is 31 then I want this statement to look like
Range("C31'").Select
View 3 Replies
View Related
Jun 11, 2008
totaly new to Excel (just using two weeks) as a database
I need to number a colum, 1 to what ever, so that I can use that colum to re-sort the data base back into original order.
View 9 Replies
View Related
Jun 14, 2007
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
View 3 Replies
View Related
Apr 8, 2009
I have a number of Grand Totals that equal to Hours of Work in a day ( Based on Demand from Customer Orders)
I only have 95 ( this will be a number in a cell that I want to be able to change if needed) work hours available to me each working day.
I want each day to attempt to fill in up to 95 hours , anything more and it will push the remaining balance forward into the other cells.
IE here is what I have for the next 5 days for Totals
211.3798120.304870.0099110.734375.041828.1292
Under the 211 I want it to change to 95 and then carry over the balance to the cell under 120 , I then want that cell to change to 95 and carry over its balance to the next cell and so on down the line. I will always have 22 Working Days I want to work with. So the last day may or may not have a greater then 95 total.
The 95 part I want to be able to change that to whatever number I think I will have available to me and it will adjust accordingly through the line.
View 10 Replies
View Related
Jun 12, 2007
I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).
View 6 Replies
View Related
May 19, 2009
I have a workbook in Excel 2003 with two spreadsheet, a data sheet and a summary sheet. The data sheet contains account numbers in column A and monthly values from column B to M (April to March) for each account. Account numbers occur more than once in column A.
The summary sheet contains each account number as in the data sheet, but only once with a month column and a YTD (year to date) column. Part of my function is to report on a given month number as well as a year to date number as at that given month. For example if it is May, I need to report the results for May as well as April and May totals. Cell C 1 contains the value of the particular month (say month 1 equals April and month 12 equals March).
I need a formula or function to return the year to date results given a certain month (cell C1) for each account number. The sumif function does not help since it only returns values for 1 column. I need the formula to perform a sumif function but if I put in 2 in cell c1 I need the formula to add the values in columns B and C, if I put a 3 in cell C1 I need the formula to add the values in columns B, C and D.
View 8 Replies
View Related
Aug 21, 2013
I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?
Name
Start
End
Other data
Verizon
1/10/2010
1/25/2010
AT&T
3/2/2010
4/14/2010
View 1 Replies
View Related
Sep 22, 2006
I have 6 columns of data laid out as follows,
columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))
View 9 Replies
View Related
May 7, 2013
I have 2 Columns of Holiday Dates (Column A is the "FROM" date; and Column B is the 'TO' date) and Column C is Peoples Names corresponding to the Holiday dates:
ie:
COLUMN 1 ; COLUMN 2 ; COLUMN 3
A1= 2 Jan 13 ; B1= 10 Jan 13 ; C1= Tom
A2= 4 May 13 ; B2= 10 Jun 13 ; C2= John
A3= 7 Jul 13 ; B3= 10 Jul 13 ; C3= Tom
A4= 3 Aug 13 ; B4= 25 Aug 13 ; C4= John
A5= 6 Dec 13 ; B5= 15 Dec 13 ; C5= John.
I am trying to figure out a formula (eg SUMIF) to calculate and summarize the number of Days Holiday based on a person's name (eg Tom's total Number of days Holiday days) based on the above column configuration of Dates and Names.
View 4 Replies
View Related
Jun 6, 2007
Is there a way to find the minimum value in column B that corresponds to the two occurances of "Yes" in column A.
Column A Column B
NO 1
NO 4
NO 7
YES 6
NO 3
NO 9
Yes 2
[Note: the numbers are in column B.]
I basically want to return the # 3 from Column B.
View 9 Replies
View Related
Nov 6, 2008
How can I count the number of cells in a range which contain a specific string?
I'm trying to use SUMPRODUCT to no avail:
=SUMPRODUCT(--SEARCH("RSC",I3:I3000)>0)
View 9 Replies
View Related
Aug 10, 2006
I'd like to count the number of time Y or Yes appears in a column. In a different cell I would like assistance in figuring out what formula could calculate the percentage of attendance based on the total Y's or Yes's.
View 3 Replies
View Related
Oct 30, 2009
OK I have included the spreadsheet I am working with and I will try really hard to explain exactly what i want and where my problems lie, so please bear with me.
The work book is separated by months. The individual spreadsheets can have multiple entries in each cell, I have a code running to automatically pop up a comment text box for each entry into that cell to separate each occurrence.
Now I have a separate sheet that will be showing how many times each item has a "hit" or occurrence for the entire month. That's where I have the problem. I don't want to go and count every time i made an entry, excel should do that. The problem I am coming up with is that I can only get it to calculate the one occurrence per cell which does me no good if there were three separate occurrences in that cell.
View 6 Replies
View Related
May 12, 2009
I would like to take the data in the first table and put it into the format of the data in the seconde table "Using EXCEL Functions" i.e. Not VBA code/MACRO. Note there are multiple occurances of some names in the first table with different fruits.
Name Fruit
MarkOranges
SteveApples
RoyPears
TedTomatoes
JohnApples
JohnPears
SteveTomatoes
RoyOranges
AlbertOranges
MarkPears
RoscoeTomatoes
View 9 Replies
View Related
Feb 5, 2010
Is it not possible to use entire columns references when you're looking up one value and wanting it to return multiple corresponding values? For example, my formula seems to work fine when I use something like:
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$10,ROW($A$1:$A$7)),ROW(2:2)),2)
(with an array)
But if I change $A$1:$B$7 to simply $A:$B (and the rest to straight column references) it gives me a "NUM! error. I need my range to reference a dynamic range because values will be added to the table.
Basically, I need a formula that will return multiple occurances of the same lookup value.
View 9 Replies
View Related
Apr 28, 2009
I am trying to find the most occurances in one column of the same word (List of Suppliers) and return how many times that name cropped up, would like to use a single formula to do so and not have another column with numbers in it if possible.
View 9 Replies
View Related
Feb 8, 2010
I need to produce a summary of the monthly spend with parts suppliers for a number of vehicles, with each vehicle having it's own worksheet. The suppliers' names are in column C with the costs in column H.
So what I need to do is sum the results of column H on each worksheet when the supplier's name (which is on the same row) matches "Triple 7" and it's in the same month. The idea is to produce a sheet which displays the total spend each month with our suppliers.
View 9 Replies
View Related
Jan 29, 2013
I've got a sheet where I've got products in one column.
The second criteria is "week #" for which the row remains consistent but the column varies e.g. "week 8" could be in cell L12 today and in L18 tomorrow.
I would like to return the value of the cell at which a particular product and week intersect. e.g. if "product a" is in B20 and "week 8" is in L18, I want the value of cell "L20" returned.
Product to determine Row #, Week to determine Column # for returning value.
If they interest multiple times, I would like to sum up the values intersecting.
Neither product nor week # is unique in the sheet.
View 5 Replies
View Related
Apr 23, 2012
I am trying to write a formula, I have 6 sets of criteria with a lower and higher range, if the number falls within the criteria I would like it to return the Alpha number,
eg, 104, will return D
MinMaxReturn030A3160B6190C91150D151240E241360F
View 7 Replies
View Related
Mar 1, 2014
I work on a daily basis with spreadsheets in excel. The number of columns is the same, but every single spreadsheet has a different number of rows. I recorded this macro in a table with 1196 rows and I would like to use this macro also in other tables with a different number of rows.
Sub City2()
'
' City2 Macro
'
'
Selection.Copy
Cells.Replace What:="POMPANO", Replacement:="Pompano Beach",
[Code].......
View 12 Replies
View Related
Jun 14, 2009
I've got a column of numbers that represent the number of overs bowled in games of cricket. Whilst these are whole numbers (eg. 34 overs + 34 overs) the addition isn't a problem, but when they are incomplete overs (eg. 34.4 overs + 34.5 overs) then the addition if out of kilter as it sums them in base 10, and not in base 6. (As there are six balls in an over, not ten for anyone who doesn't know!)
View 2 Replies
View Related
Mar 7, 2012
I have entered this value into a cell on my worksheet. It is referencing a cell in another worksheet on a shared drive on my network.
='\servershare$[doc1.xls]Sheet1'!$A$1
All is fine until I re-open the document, whereby it has changed to:
='\serverusers$share$[doc1.xls]Sheet1'!$A$1
I have been advised to set the hyperlink base as \servershare$
...but this clearly does not work.
View 4 Replies
View Related
Apr 21, 2006
i have used the database from j & r solutions. i have altered it slightly to suit me. the database works fine apart from when using the find all button it will only return 4 entries. if there are more than 4 entries it returns runtime error 9.i have zipped up the code and marked where the error is shown when i debug
View 2 Replies
View Related
Dec 3, 2008
What If we had to replace any number..
Lets say, if we had to seperate NUMBER TEXT NUMBER in different combinations....
B2 contains values like these then
TOM CRUISE 12
TOM 5879 CRUISE
TOM CRUISE 123456789
123456789 TOM CRUISE
123 TOM CRUISE 456
[ = SUBSTITUTE(B2,"1234567890","") ]
I am at my wit's end pondering over it?
How to make the SUBSTITUTE function work for each individual digit?
View 11 Replies
View Related
Dec 11, 2013
I'm working on a charting application that uses Office charts. I'm wondering what are some of the main reasons people change the base unit from the default, and how common that is.
Some reasons I've identified on my own:You're comparing different monthly metrics, but they could be recorded on different days of the month.Get a zoomed-out view.Compare the max value of each month.
View 3 Replies
View Related
