I have a list of payroll data (Hours, Rate per hour & Total) for a number of workers. I want to summarize the data in ranges of hours and by ranges of rates in a table. This will then show me in what ranges of Hours & Rates is creating the most value (Total). See attached RangeSummary.xls for example data and output.
View 3 RepliesThe following table is used in the sumif.
I would like the sumif to use the range with the names, and the range with the dates.
I have a GUI in which the user enters a name and a date and the $ amount earned for that week should appear.
The criteria is the name and the date.
******** ******************** ************************************************************************>
Microsoft Excel - Business Book.xls___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutP20=
IJKLMN3******4michaelb1*14$400.007/21/20025Michaelb1215$450.007/21/20026
[Code] .........
SUM formula. My spreadsheet has roughly 8000 rows of data listing client info for how many commercials were purchased on Cable Networks a (a simplified example)...
ABCDE1ClientNetworkDay-partPrioritySold2Client AHGTVAccess35063Client BHGTVPrime45014
Client CHGTVDaytime30095Client AHGTVPrime45096Client DHGTVPrime35037Client BHGTVPrime5502
What I am attempting to do is on Sheet2 summarize the commercials sold based on a starting Priority. So if the priority code in Column D is 350, I need to sum all spot sold at a priority of 350 and higher for each day-part and Network
ABCD1NetworkDay-partPrioritySold2HGTVPrime350 and up
?3HGTVPrime400 and up ?4HGTVPrime450 and up
?5HGTVPrime550 and up ?6HGTVPrime600 and up ?7HGTVPrime650 ?
I've attempted to create helper columns to tie all the necessary PR codes together and then SUMIF by the different helper columns, but I'm not getting the correct result. Instead of getting "15" for Sheet2, D2... I'm just getting "3"
I am attempting to count records based on 3 criteria in 3 different ranges and am hitting a wall. My formula will be on a different worksheet as it is the summary sheet.
Worksheet "Cases"
Column E - Type
Column F - Month
Column G - Year
Worksheet "Recap"
Recap month will be input into Cell I5 eg. "6" for June
Recap year will be input into Cell K5 eg. "2009"
Cells N10-N15 is a list of Types
Summary Cell J10 will need to count if
1)worksheet Cases column E = worksheet Recap N10
2)worksheet Cases column F = worksheet Recap I5
3)worksheet Cases column G = worksheet Recap K5
I have a worksheet that has data in columns a through d. Acct#, Name, Usage Code, and Other Code. Acct# and Name are self explanatory, usage code is a two letter code, and other code is a field I added to use with my macro. A specific account number could be repeated many times, but it is the usage code I am needing to work with here. My acct numbers are sorted in numerical order and column d has a 1 each time a new account number appears and a 2 if it is a repeat. I am wanting to copy the account number of any account that does not have a specific usage code listed at least once. That specific code is "ta". I am wanting it to only list the account number once in column f starting at row 2. Here is my code that is not working.
Sub test()
Dim CopyNum As Integer
Set CopyNum = 2
Dim Switch1 As Integer
Set Switch1 = 0
I have a cell that I need to make a calculation if certain other cells =Y, or to show "0" if the other cells =N. I can do the calculation for the Y part but how do I add the N part of it? Here is what I have:
=SUMIF(J19:J40,"Y",H19:H40)*0.08
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View RelatedI need a formula that sums the values with 2 different criteria and 2 different look up ranges
View 9 Replies View RelatedI am using a work sheet where I want to count if Column A has one criteria and column B has another
example
column B states is used to track contracts it can be vendor
column L tracks status it can be open, received, or closed
I want to count the cell if the vendor name matches and the status is open
also if that is possible is the same possible with 3 ranges and criterias?
What I'm looking for is that it ADDS the values, when 2 or more ranges and criteria are met. I don't think it's possible through the SUMIF because it allows "range,criteria,sum_range." But I know there's another way around, if so, please demostrate in a basic formula.
Like.. I have something on A:A (name) and B:B (points), and the formula must meet the 2 ranges & criterias (name & points) to add the values in C:C (money).
i want to do is i have an excel spreadsheet...and i have two columns in there that are called Representative and one thats called Result....The Result column has Closed, Rescheduled, and Not Interested....so what i want to do, im looking for a formula that will calculate how many CLOSED are from a certain Representative....so if i have 5 Closes for John...im looking for a formula to calculate those 5 for John...but i dont know what formula nor how to use two diff. ranges with two diff. criterias....
View 7 Replies View RelatedI have a spreadsheet where my dropdown menu is taken from a master list. The master list is also broken down into several catagory specific lists.
I need a macro that will take the individual cells in the worksheet that use the master drop down list, check them against the contents of the specific lists (defined names) and assign a cell color fill based on wich list the data was found in.
lets say my master list is:
MASTERLIST
apple
orange
bananna
lemon
pork chop
sirloin
chicken wings
carrot
potato
onion
sage
thyme.............
I have a sum part of which is $I$4:$I$500.
The 500 part of the sum is constantly being manually changed using edit replace because of a growing amount of data.
In cell A1 ona another worksheet within the workbook I use the Count function to count the number of rows containing data. This cell is named DataCols
Is it possible to combine $I$ with the named Range DataCols?
I have got this far - ="$I$"&INDIRECT("ColNo") which returns 500 if I remove "&I$"& in front of it.
I am trying to write an array using =sumproduct. I cannot get the second array to work. Whenever I split up the arrays they work fine separately, but not together. This is what I am putting. Any help is greatly appreciated. I am not sure if I am even using the correct formula.
=SUMPRODUCT((B37:B51="Math")+(F37:F51)+(I37:I51="Math")+(M37:M51)),[(B18:B31="Math")+(F18:F31)+(I18:I31="Math")+(M18:M31)]
So basically, I am creating a transcipt. So in column B if the subject is Math, I want column F to be added and so on and soforth. See there are more arrays, because it is broken down to grades 9, 10th, 11th, 12th. And what is being added is their credit (.5 or 1) for the class for each year.
IM trying to add the result of multiple ranges sharing a single criteria and have had no luck. I am trying to get the range from every other colum and have the total qty of the selected criteria display in a single cell. The best way to describe is countif with multiple ranges and a single criteria. perhaps countif isnt the answer, it is the only way i know to describe the issue though.
View 3 Replies View RelatedI need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:
User would input the following information:
Zip Code: 56559
Pallets: 3
Weight: 1200
The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".
From Zip Code
To Zip Code
Pallets
Weight From
Weight To
[Code]......
I have two lists of values in separate columns with condition in adjacent cells, all on the same sheet
List 1 consists of numbers from 1 to 12, column to the right is either blank or Free
List 2 consists of numbers from 13 to 24, column to the right is either blank of Free
List 1 starts in A2, B2 is either blank or Free
List 2 starts in D2, E2 is either blank or Free
In column G, from G2 i want it to list all values from List 1 and List 2 where their status is Free
I am trying to do index match function with criteria ranges in another worksheets. The code kept on giving me " #Value!"..
Here is the code:
Sub test1()
Dim myvalue As Variant
Dim wsname As String
Dim i As Integer
Dim j As Integer
Dim r1 As range
Dim r2 As range
Dim r3 As range
Dim r4 As range
wsname = "CustomerIDbyParts"
[Code] .......
The resulting cell of F5 kept on returning #Value!.. I suspect that my syntax for looking up the ranges from another worksheet is wrong?
My setup is - excel 2003 sp3 / windows xp
On one sheet (Data) I have a list of action items, each with owner; target date; classification and in some cases revised target date. I'm trying to report on these fields and provide a status, by owner and classification, of how many are overdue; due this month; due next month; due beyond 2mths.
Using a SUM array formula on another sheet I can count the number that are overdue based on date; owner; classification; and target date, but can't find a solution if there is a revised target date. Any guidance you can give would be greatly received.
=SUM((Data!$L$3:$L$27=D$18)*(Data!$P$3:$P$27=$C23)*(Data!$N$3:$N$27
Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:
SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst
FROM CPS.dbo.uvVisit uvVisit
WHERE (uvVisit.Date Between ? And ?)
ORDER BY uvVisit.FacilityListName
The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).
However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.
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!
I want to calculate a total of the sum based on range.
Ranges
0-1000 = multiply total cell value x 0
1000 - 1300 = multiply total cell value x 1
1300 = multiply total cell value x 2
So if the Cell value = 1183
The formula would calculate 1183 x 1
If the cell value = 1283
The formula = 1283 x 1
and if the Cell Value = 1301
The formula would then equal 1301 x 2
These calculations need to be done in a single cell.
Sum based on date ranges. I am creating one logical formula.
I have an excel sheet with two columns.
A1: Date B1: Time Spent
Date
Time spent (in Minutes)
Total
[Code].....
As per above figure I want to sum based on the same dates. I.e A2 has date 1/26/2012 for which first entry is 12 so total will say 12 in C1, for the same date second entry is 14 so 12+14=26 is there in total at C2, for the third and last entry of 1/26/2012 it's 16 so total would be 12+14+16 = 42 in C3 which is grand total for 1/26/2012. I want to fill entire C column with some logical formula so that the logic holds true for various dates.
I have tried to use the IF formula like =IF(A1=A2,SUM(B1:B2)) but it fails at third cell. I have tried several ways to do this but no luck so far ....
Any way write a conditional format formula to shade a cell based on two ranges.
Here is what I need:
cell b4 should be colored in if n4:xfd4 = today's date AND n3:xfd3 = "Time Off". The part I am having trouble with is that it should only shade if today and time off are in the same column.
Here is an example:
This one should shade B4(note: Today is 08/13/2013):
time off
late
08/13/2013
08/10/2013
This one should not:
late
time off
08/13/2013
08/10/2013
This is what I came up with so far, but it shades the cell if either show up in the entire range:
=AND(MATCH($G$1,$n$4:$XFD$4),MATCH($H$1,$n$3:$XFD$3),1)
Note: g1 contains =today() and h1 = "Time off"
So formula is wrong.
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedI need a formula that will give me a persons name that is stored in Column B based on the highest value within 5 named ranges. Each named range is a selection of every other cell in individual columns i.e. named range junior1 is cells in column E, named range junior2 is cells within column H and so on.
I've attached the worksheet as it as present.Sample Running Scores 3.xlsx
Cell Y3 shows the highest individual score shot (not handicap score) from columns E,H,K,N,Q (or more accurately the named ranges Junior1, Junior2, Junior3, Junior4, Junior5 within these columns). Each of these columns represents a different round in each league, in this case a Junior National League. I now need Y4 to show me the name of the person that shot the highest individual score, in this case Person 21.
I know I need to use a combination of Index & Max in some way but I can't work out what or how.
i edited this thread becuase I realized why I was getting the wrong results, however, I am completely clueless on how I need to accomplish the task below.
I need to apply formatting based on five named ranges on another sheet. if the values in cell F1:F1000 appears in the named range "datestore" and the corresponding value in range h2:h1000 appears any of the named ranges "date1" through "date5" I would like it to fill the cell yellow.
I know how to get the last row for columns, usedrange... etc but I don't know how to get the lastrow based on multiple column range
For example, find the last row between columns A:M but IGNORE N:O however we still want to check/get the last row for P:Z
I am having trouble with IF, ELSE and END IF statements. In Column H I am trying to copy over rows to sheets based on value according to ranges. I am trying to use the code below but everything seems to get copied in to the first sheet "0-500". d= worksheet name.
View 6 Replies View RelatedI have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter.
"| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs
( | are cells)
In another cell, i would like to merge them together like so
"A01-A07" are for Joe Bloggs
And also as follows
"| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith
would like this to be
"A01, A03-A05,A07" are for John Smith.
So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.