Sum Of Non-sequential Fields Meeting Multiple Criteria
Nov 2, 2011
I would like to sum fields IF the STAFF Name is the same AND the PROJECT ID is the same...
I have included sample data below:
Example: Sum the total TIME for GRIFFIN, TROY for PROJECT 1 (1 row). Separately Sum the total TIME for GRIFFIN, TROY for PROJECT 2 (2 rows).
When this sheet goes live there will be hundreds of rows with many instances of the same project id and same Staff name.
Project IDStaffTimeInt AmtExt AmtInt TtlExt TtlTask SavingsTotal Project Savings1Griffin, Troy2.25$30.00$60.00$67.50$135.00$67.508501Harvey, Deborah1$30.00$60.00$30.00$60.00$30.001Johnson, Lenworth6$30.00$85.00$180.00$510.00$330.001Kelley, Marijane6.5$30.00$95.00$195.00$617.50$422.502Griffin, Troy12$30.00$60.00$360.00$720.00$360.008702Griffin, Troy17$30.00$60.00$510.00$1,020.00$510.00312$45.00$55.00$540.00$660.00$120.00240
View 3 Replies
ADVERTISEMENT
Aug 10, 2009
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
View 2 Replies
View Related
Dec 18, 2006
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
View 9 Replies
View Related
Sep 27, 2012
I have long series of data in 8 columns (B, F, J, N, R, V, Z, AD) starting at row 5 down to row 86404. I want to find the total median for each row, where a condition is met for that row. The condition is if number>10 and the number is located in row 3 for each column (i.e., B3 for column B). So, if B3, J3, R3 are all >10, then I want to find the median for all the numbers in the columns B,J,R.
I assume there is an array formula that could do this? I've tried a few solutions with no luck.
View 4 Replies
View Related
Dec 13, 2006
In my code I am searching a spreadsheet for certain states and deleting rows that have states I want to exclude. Is there a way to do this with a list of states, instead of having to make many for loops?
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "PA" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "TX" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
View 5 Replies
View Related
Jan 15, 2008
I have what I thought would be a simple task, but after almost a day of searching this and other sites, I've gotten no where. I have two issues that are similar. First, I need to edit my VBA code to count the number of blank cells in a column. Here's the code I'm starting with...
View 3 Replies
View Related
Feb 29, 2008
I have an extremely large CSV file that I am opening via Excel. Can anyone tell me how to delete a row using multiple criteria. For Example, i want to delete the whole row if a certain cell doesnt equal GA SC or NC. I have seen how it can be done using one criteria but not multiple ones.
View 8 Replies
View Related
Jun 30, 2009
I have a "SUMMARY" sheet and then five other sheets. I need to be able to extract the data which is >0% from each of the individual sheets and capture the results in the relevant section of the "SUMMARY" sheet.
For example, the data from sheet "PAYG" would need to be entered into cells F148:AJ155.
In each instance it is just the "model" detail I need to capture (column D)
View 5 Replies
View Related
Apr 17, 2008
I have a large file of invoices, and some have been paid. The problem is, sometimes it is not paid against the correct invoice, so I have a lot of credit and debit that should offset each other. I want to go through the sheet and highlight all the rows that has a matching ID and an a 0 value when total amount column was added up to another row. I would like it so that it highlights a different color for every pair of offsetting amount, but the same color highlight is fine too.
View 2 Replies
View Related
Jun 26, 2014
I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.
I have tried different formulas, but fast running out of time
I have attached a modified sample from the main spreadsheet
I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.
View 7 Replies
View Related
Feb 24, 2014
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
View 2 Replies
View Related
Jan 23, 2009
I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
View 2 Replies
View Related
Jul 31, 2007
In the formula (range, criteria, sum_range), I have a fixed range and a fixed summary range for each column, i.e.: ($F$3:$F$805, "criteria", O$3:O$805).
HoweverI am trying to sum up units by income level (columns D, E, and F) using information from elsewhere on the sheet. I am doing this for each city, which entails changing the criteria for all of the cities I am using three times (once per column).
Is there a simple way to autofill the criteria? They are just names of cities, all in the same column, COLUMN B. Or do I have to type each individual change?
View 10 Replies
View Related
Jun 20, 2014
I'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.
View 3 Replies
View Related
Nov 12, 2012
I'm doing an assignment. This would be very simple w/ filters but I have to do it thru formulas.
I have a bunch of rows w/ various hotels. Row 14 is Rolling Meadows, Row 15 is Lakeview Apartments etc.
Near the top I have to add the number of times Rolling Meadows is mentioned, in the cell below Lakeview apartments, etc.
I tried a sumif formula but that doesnt work. How would you do this? This is what I wrote, I got 0
=SUMIF(B14:B26,"Rolling Meadows")
That's part 1. In part 2 I have to calculate the number of units in that building that are occupied.
View 9 Replies
View Related
Mar 26, 2013
we would like to get results from a formula that looks at several cells and provides the cost for a product.
Example
If we choose
Cell A3=Transport (from drop down list)
Cell A4=Entrance Facility (from drop down list)
Cell A5=Bandwidth (from another drop down list)
returns the cost for this product in cell A6
We would also like to restrict the lists to the different catergories: if transport is selected you only have the option of 2 of 5 facility types that will work with transport products. Do I need to separate my lists?
View 1 Replies
View Related
Oct 6, 2006
I need to get the sum of a column where two conditions have to be met. I have tried doing something like: Sum(Sum(AND(V1:V1500="BEBLT", H1:H1500=17) +-Sum(AND(V514:V1512= "NVLIV", H514:H1512=17)))
In a nutshell I want to find the difference between the sum of a column whose criteria is 17 and BEBLT and the sum of another column whose criteria is 17 and NVLIV. This has been extremely frustrating since I am using SQL commands to pull the info from the workbook. Typically I would use
"Select SUM(Z) from Sheet1$ WHERE (H1:H1500=17 AND V1:V1500);"
Unfortunately for me my SQL command brings back an error so I must resort to finding the total in excel and then using the command to bring it into my VBA.
View 4 Replies
View Related
May 27, 2007
I'm trying to use the SUmif with Offset, but the width argument of offset doesn't
seem to work within the SumIf function. I have 14 columns
A Employee
B department
C-N jan through dec salaries per employee per month
41 rows
1 titles
2-41 names of employees
now in May I want to know the salaries per department for the month may only, but also cummulative from jan up to may. Getting may per department works fine, but getting the cummulative doesn't seem to be working
=SumIf(B2:B40;"account";offset(C2;;4)) gives me the sum of department
Account in the month of May
=SumIf(B2:B40;"account";offset(C2;;;;4)) starting point zero (= january) plus 4
columns width, just gives me January.
I've tried several options, but every time he only sums 1 column. Without the SumIf it works fine but for the whole company, not per department ofcourse.
View 4 Replies
View Related
Jun 9, 2007
how a SUMPRODUCT function can resolve this. In this sample I want to have 1120-32188 & 1120-32188W be treated the same for the Total sum qty. In this case =10.
This will compute but how do I include items in column A that have 'W' suffixes for computing Total Qty? Formula: =IF(A2=A1,"",SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100)))
View 4 Replies
View Related
Aug 9, 2007
I have an Excel sheet with 3 worksheets. On the first sheet I want to enter a formula that will check the third sheet. On the third sheet I need to compare all the entries in column N with a value in Cell B2 of the front sheet. If these match then I need the formula to add up the relevant values in column N. (column N has a 4 letter code, I am only interested in comparing the first letter, as such the value in cell B2 is H*, then in column U it has numbers of minutes, I want a total of the minutes of all those entries with a code that begins with H)
View 4 Replies
View Related
Mar 27, 2008
What I have is 4 columns of data broken down as follows Column A = Date Range B = Name C = City D = Amount of People.
What I want to achieve is to be able to go through my list of data and for all rows that match specifically matches A,B,C it will total column D and output to a cell. I also need for it to reflect the name somehow .. so in attached example A2-C2 matches A8-C8 so I would need the output of D8 + A8 (which should be 54) but I need for this to some how reflect a name combination City + Name. So I would know that abc & California have 54 people and that def and New York have 56.
I have attached a basic spread sheet of data; keeping in mind that in the complete data there could be hundreds of combinations for data to be matched.
View 8 Replies
View Related
Jan 29, 2014
I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.
I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.
Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.
I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.
View 3 Replies
View Related
Nov 6, 2009
On the attached file, I would like the values from the Transactions worksheet to show up in the appropriate cells in the XYZ Expenses worksheet. The catch is, I would like the XYZ Expenses worksheet to only show the values from the past week. Past week could be literally, but I'm guessing that might not be possible and it would have to use the past week from the most recent transaction date - which would be fine. So if it worked right, XYZ Expenses > F7 would show -$80, which would be from Transactions H3+H6 (and not -$130, from Transactions H3+H6+H9).
View 3 Replies
View Related
May 7, 2005
Problem:
Range A2:C10 contains the login and logout times of various ID's.
Each ID could log in and out a number of times a day.
How could we find the first time a specific ID logged in and the last time that same ID logged out?
Solution:
For each of the ID's in range A2:A10, enter two Array Formulas.
To find the first login time (Column B) enter the following formula:
{=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10
To find the last logout time (Column C) enter the following formula:
{=MAX(($A$2:$A$10=A14)*($C$2:$C$10))}
ID______Login Time______Logout Time
1 ______02:40___________03:10
2 ______00:15___________03:20
1 ______06:20___________09:30
3 ______09:14___________11:05
4 ______11:00___________19:30
2 ______04:05___________06:55
3 ______12:08___________17:17
1 ______10:00___________16:20
2 ______08:12___________12:33
ID______First Login Time______Last Logout Time
1 ______2:40__________________16:20
2 ______0:15__________________12:33
3 ______9:14__________________17:17
4 ______11:00_________________19:30
View 9 Replies
View Related
Jan 1, 1970
I can get the result. It shows #num! in cell F2
View 14 Replies
View Related
Jul 25, 2012
A column W5:W9999 contains attendance from either of 0, 1, ........31 of employees.
How to count no of employees meeting certain criteria like:
Total no. of emp with values >0.
Total no. of emp with values >0 but < 16.
View 9 Replies
View Related
May 11, 2008
I have been having problems all morning and have not been able to solve it!
I am sure this is very simple....
What I would like to do is assign the result of a sum to a variable. This part is easy what I cannot fathom or find any help with is the multipule criteria bit!
Here is the detail.
Column A - Employee Number
Column C - Area
Column F - Type
Column D - Amount paid.
I would like to sum the amount paid based on the 3 criteria, Employee Number, area & type and assign this to a variable to be used later in a sub I'm playing with.
View 9 Replies
View Related
Apr 21, 2009
I want to write a sub that captures the existing lists in the attached file in two arrays then create two new arrays of customer names and amounts spent for customers who spent at least $500. After these arrays have been filled, I want to transfer their contents to the columns D and E.
View 3 Replies
View Related
Sep 28, 2006
im trying to set my workbook up so that it is updating itself daily. Currently it has about 4000 rows of invoices from multiple departments. What I want is a formula that when an invoice reaches 31 days that it goes to a separate worksheet. I don't want it deleted, I just want it to be moved in to a worksheet called 'WatchList'.
Any body know how this can be done. What Ive done right now only puts the row in the same row from the main sheet. So if row 2000 reaches 31, it shows up on row 2000 in 'WatchList'. Im wondering how I can move row 2000 to the top and whatever other row to the top once it reaches 31.
View 4 Replies
View Related
Nov 1, 2006
I would like to seek help on how to delete a cell that has data in it and that has colourfill. E.g. Cell A1 shows "Occupied" and Cell A1 has a blue colour fill.
Need help on how to delete that cell's data and remove the blue colour without having to manually do so.
View 4 Replies
View Related
