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
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?
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.
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)
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.
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.
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).
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.
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
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
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.
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.
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.
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
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.
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.
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.
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.
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,
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.
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!)
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
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?
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.
