How To Sum Column Base On Few Criteria
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
ADVERTISEMENT
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
View Related
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
Mar 5, 2010
NDL234561100015657
NDL546891100015658
NDL666771100015659
OSL342561200018756
OSL234441200018757
PIL44994 1500014566
NDL678991100015600
NDL555551100015664
above is col A which has all codes and second is col D which has respective numbers. i want help with some macro which can combine (seperated with a comma) all the numbers in col D based on first three letters of each cell in col A.
see like for NDL, i will have 1100015657, 1100015658, 1100015659, 1100015600,1100015664.
see like for OSL, i have 1200018756, 1200018757.
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
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
Aug 21, 2013
formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.
View 4 Replies
View Related
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
Aug 25, 2014
I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).
Sheet 1 (order)
A
052611
052806
052843
Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N
how I can write is in a formula?
View 7 Replies
View Related
Mar 4, 2008
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
View 5 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 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
Dec 18, 2013
I have done many research online to cater to my require but fail to accomplish it. I need to determine the decision base on ranking of the number in the Rank column. which is than reflected in the calender. But per day basis, it needs to only be green for the top "number of quota set per day" highest ranking. I have also attached the sample for a clearer understanding
Sample.xlsx
View 4 Replies
View Related
Jul 17, 2013
We have database (attached here). I want to copy the name of candidates base on their Status to their designated tab. example Diana Longoria name will be copy to "Waiting" Tab and if I change the status to "Rejected" it will be copied to "Rejected" tab and remove name from "Waiting" tab.
View 12 Replies
View Related
Jan 12, 2009
I am working on a spreasheet that will automatically calculate the interest rate, loan to value advance, and other parameters from user inputs.
I have found a way to get the calculations to work correctly, but I have about 50 lenders to input - all with different rates and lending guidlines with respect to loan to value advances.
I am sure that I am going about it the hard way and I have no problem going at this to get it right.
I have attached the spreasheet I've started. I've only got one lender completed thus far. So if you need help sleeping at night, go ahead and see what I've done (yes it's boring).
Edit note: I don't know if using Access would make this an easier project to tackle but it is an option (I'll just have to learn
Access if that's the case - I've never used it).
View 3 Replies
View Related
Jan 18, 2010
I have a formula that calculates only seconds and frames. The frame rate is 75 fps (0-74).
e.g., 49.50 is 49 seconds 50 frames. It will not parse Minutes.Seconds.Frames, e.g., 1.49.50 is 1 minute 49 seconds 50 frames.
What my existing formula does is converts the number to all frames then converts the answer back to seconds and frames.
I need this formula to include minutes in its calculation.
A copy of the spreadsheet is here: ...
View 14 Replies
View Related
Dec 27, 2012
I am trying to calculate a new base pay rate, but I need it make sure it is at least brought up the new minimum and capped at the new max of the range where applicable. The increase is based on 10%
So here are the columns used:
L = Current Base Pay
Q = New Min of the range
S = New Max of the range
U = where I want to calculate a 10% increase of L, but ensuring it is brought up to at least the min (Q) or not over Max (S). In other words if my min is $12 and Max $18 and my new base pay is $16 -- then I am good. However it if is $11.50 I need the formula to return at least $12. And visa versa -- if the new rate would be $18.50, I need it to return no more than $18.
View 3 Replies
View Related
Oct 4, 2013
I have a macro that delete's a row when a cell in that row contains a specific value. I am unable to make the macro work automaticly when the file opens.
Sub Delete_Rows()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A1:F400"), ActiveSheet.UsedRange)
For Each cell In rng
[Code].....
View 3 Replies
View Related
Feb 3, 2008
I hv following table :-
Summary Report FGH1how urgent How ImptRank2highHigh 3midhigh 4LowHigh 5highmid 6Midmid 7Lowmid 8Highlow 9midlow 10lowlow Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4
I need code , when run it will fill in the ranking number :-
Summary Report FGH1how urgent How ImptRank2highHigh13midhigh24LowHigh35highmid46Midmid57Lowmid68Highlow79midlow810lowlow9Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Feb 11, 2009
I have multiple worksheets that call one userform.
Each worksheet has a specific word in cell J1 that matches a worksheet name.
How do I select a a sheet based on a cell value in J1?
Code: .....
View 9 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
Dec 7, 2006
I need to read consantly changing shift time/ covered data from a Pivot Tables pivot chart and populate this data into number of shifts covered/ uncovered. This information is then put into a chart over a 24 hour period (from 0700 to 0700). I have been populating the data from the pivot chart by hand by referencing the number of shifts in the covered line and dragging it to correspond to the shift time data part. I then have to do this for the uncovered shifts. As the data in the pivot chart is constantly changing, i need to do this data ransfer 'automatically'. I have started to look at and learn VBA, but i am getting nowhere fast. I enclose a worksheet (blank) to give you an idea fo what i am trying to do.
View 4 Replies
View Related
Jun 27, 2007
I have a chart that I want to be the same across multiple worksheets. The data ranges don't move, but the data may be different. It is cumbersome to go and retype the name of the sheet every time this chart is placed.
I have tried using named ranges. My named range X is !$A$1:$A$30 so that it will refer to the active sheet. If I place this in cells on the spreadsheet, it works. If I place "=X" in the values entry for the source data of the chart, I get a formula error.
View 9 Replies
View Related
Jun 7, 2014
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
View 2 Replies
View Related
Aug 20, 2012
I have 1 workbook with 2 sheets: sheet1 and sheet2.
Sheet 1 format:
A
B
C
D
[Code].....
My sheet2 has 5000 rows and need to copy to sheet1.
View 9 Replies
View Related
Mar 3, 2014
Cell B23 contains an invoice base price in $'s formatted to 2 x decimal places Cell I4, I5,I6,I7,I8 all contain various percentage discounts formatted to 2 decimal places
I am trying to creat a formula in cell I23 that will subtract the discount of each of "I" range cells - lowest number to highest- in order. But each discount has to be subtracted from original invoice value in cell B23
Example:-
Cell B23=$1660.00
Cell I4=14.00%
Cell I5= 3.50%
Cell I6= 3.00%
Cell I7=1.50%
Cell I8=2.00%
I have tried using =B23*(1-I4)*(1-I5)*(1-I6)*(1-I7)*(1-I8) But this appears to be a cascading discount By my manual calculations answer should be $1261.60 But above calculation gives me $1289.94
View 1 Replies
View Related
Feb 25, 2009
what i trying to learn is that im trying to get the delivery price calculate base on the delivery area(F10) and the total qty of the items(G10).
but i've tried with Vlookup, IF, lookup, Hlookup function and i still cant manage to get the right one to put the data in the H10
View 4 Replies
View Related
Nov 9, 2009
I have this macro it save to specific location but if the file name exist then macro fails or wants to overwrite existing file I will like to make this macro to add a number
So It will look like this
DISCONNECTED # 11-09-09.xlsx
DISCONNECTED # 11-09-09 2.xlsx
DISCONNECTED # 11-09-09 3.xlsx
DISCONNECTED # 11-09-09 4.xlsx
DISCONNECTED # 11-09-09 5.xlsx
View 3 Replies
View Related