# SUMPRODUCT & Dynamic Range: Total All The Blank InDates For Each Technician By Employee Number

Feb 22, 2009
I have what I thought was going to be a simple function to create and, after a week of getting more error messages than I've had in a year, I'm ready to throw in the towel. Situation: There are two worksheets in the same workbook [Excel 2003]. One is for "Posting" details about technicians and their work orders. The other is a "Report" that summarizes the number of workorders open, closed, etc.

In the "Posting" sheet, I have created dynamic name ranges for two columns: Technician (4-digit text field) and InDate (date field that, if blank, means that the technician has not closed this work order. My ranges are set up like this:

Technician=OFFSET(Posting!$B$2,0,0,COUNT(Posting!$B:$B),1)

InDate=OFFSET(Posting!$D$2,0,0,COUNT(Posting!$D:$D),1)

So all I need to do is to total all the blank InDates for each Technician by employee number. I've tried COUNT to SUMPRODUCT. A co-worker got it to work with using...=SUMPRODUCT((ISBLANK(Posting!$D$2:$D$65307))*(Posting!$B$2:$B$65307=4288))

His solution was to list almost every available cell in each column. I just can't help but think that the dynamic name range should be able to work but I can't get it right. I think I'll choke if I get one more "N/A" or "VALUE! error.

View 3 Replies
ADVERTISEMENT
Oct 14, 2007

I am trying to create a rota in excel but I am struggling to get it to add up the total number of hours per week per employee.

It get a little complicated as they work split shift so they may do 4 hours in the afternoon and another shift in the evening going into the next day, example they may work 11.00 am - 3.00 pm and 8.00 pm untill 2 am.

View 9 Replies
View Related
Aug 25, 2008

I've got two columns of numbers, such as:

(Col A) (Col B)

Cycle Bin

1 - - 3

1 - - 7

1 - - 7

1 - - 2

1 - - 5

2 - - 7

2 - - 9

2 - - 6

3 - - 7

3 - - 2

4 - - 2

4 - - 8

4 - - 2

4 - - 8

4 - - 5

I'm trying to get the total number of columns that have a specific match-up, for instance, how many cells are in Cycle 1 with Bin #7? ....

View 9 Replies
View Related
Nov 2, 2007

I have a sheet with data in A1:A50 also in B1:B50 and amounts in C1:C50

I calculate using the formula sumproduct((a1:a50="yes")*(B1:B50="RED")*(C1:C50)). How can I change this to allow for rows being added on a regular basis.

View 9 Replies
View Related
Oct 23, 2009

I am trying to sum up the total number of hours consumed on a particular activity within a specified period.

=SUMPRODUCT(($C38:$C176>=$B7)*($C38:$C176<=$C7)*($B38:$B176=$D5))

Unfortunately, the formula above gives me the total number of activity and NOT the total number of hours within a specified period. I attached the file I'm working on as reference.

View 2 Replies
View Related
Jul 16, 2012

I have a "RANKIF" formula using SUMPRODUCT:

=SUMPRODUCT(--($B4=$B$4:$B$100),--($F4

View 9 Replies
View Related
Apr 11, 2012

I have a problem with a sumproduct formula. The formula works however both workbooks have to be opened at the same time or it will not update and you get a #VALUE! error. The formula is:

=SUMPRODUCT(SUMIF([Mthlydta.xlsx]FEB!$DZ$11:$DZ$65536,Cash,[Mthlydta.xlsx]FEB!$DP$11:$DP$65536))

I have also tried using

=SUMPRODUCT(INDEX([Mthlydta.xlsx]FEB!$DZ$11:$DZ$3000,MATCH(Cash,[Mthlydta.xlsx]FEB!$DP$11:$DP$3000,0),1))

But it only finds the first value in the list Cash. Which the list of accounts for cash will grow because we are expanding and have several mergers going on.

View 1 Replies
View Related
Feb 11, 2012

The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))

B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.

Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).

The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.

I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]

View 5 Replies
View Related
Mar 14, 2007

I need to find the total number of rows down to the next blank cell (and then perform a function based on that number).

I'm using:

CountA(A1,xlDown)

Situation: I have a raw data import - each record is anywhere from 2 to 9 rows, and I need to move each row in that group into a column.

I would like to use something like:

totalRows = Application.WorksheetFunctions.CountA(Range("A1, xlDown"))

If totalRows = 4 Then

ActiveCell.Offset(1, 0).Range("A1").Select

Selection.Cut

ActiveCell.Offset(-1, 1).Range("A1").Select

ActiveSheet.Paste

etc.

View 9 Replies
View Related
Dec 21, 2013

Refer to the attached sheet which is Daily Schedule for employee. I need a formula to add hours worked on single day in cell C2 for Monday, E2 for Tuesday, G2 for Wednesday, and so on.

Every day we have Clockin_Clockout info for each employee as shown for employee a & b.

FYI : I am using below formula to add employee hours for the week as (formula in cell R4).

[Code] .....

View 14 Replies
View Related
Aug 4, 2006

I have a named dynamic range with the source

=OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J),1)

Thia is set up as the data validation list in a cell with a drop down

list It is set so that once the drop down is activated there is the

option of selecting a blank from the bottom of the list so that if you

activate the cell in error you don't have to delete any entry you are

forced to make, you can select the blank and leave the cell blank. This

works fine until the list gets quite long at which time there is no

option of a blank.I don't know the number in the list where this starts

but certainly a list over 100 has this problem. On shorter lists the

blank is selectable. The selection is correct as when you check the

named list it shows the correct range. I would welcome any views on

solving this if it can be resolved.

View 11 Replies
View Related
Jun 28, 2013

Im working on a macro that i need to delete any row that contains 0 or blank in a dynamic range that starts at H23.

View 1 Replies
View Related
Aug 11, 2008

I am having the same problem as this thread Dynamic Ranges with blank cells.

I've recently been pointed in the direction of dynamic ranges (named ranges that use OFFSET and COUNTA to expand to cover the whole of a list, even if the number of rows used is frequently changed).

They seem to have a big drawback, in that the range will not go to the bottom of the list if the list has blank cells in (because the COUNTA for the column of interest does not equal the number of rows in the list).

A typical named range might look like this:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$25),1)

I tried to post in it but it's too old.

I have been to the link posted in that thread about advanced dynamic named ranges but inserting a row with numbers in it and then hiding it is not suitable for me as i deal with other peoples spreadsheets so need to always be working with the Active Sheet.

View 4 Replies
View Related
Jul 22, 2014

I've normally just dynamically selected a range using the xldown feature but because this row contains formula it goes always down to the bottom of the formula instead of the last cell which isn't blank.

How would i go about generating this dynamic range that stops at the first cell that contains no value (but has a formula)? Perhaps a do while loop which looped down until it hit the first blank and then assigned the cells it had looped through as the range?

View 3 Replies
View Related
Jun 10, 2014

Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.

Please see attached file : Formula.xlsx

View 1 Replies
View Related
May 19, 2014

Attached is a spreadsheet I am working on that has a series of graphs with defined dynamic ranges. I am having 2 separate issues on 2 different tabs.

1) Ranges named AirRecBra4 (and Bra10); AirComBra4 (and Bra10); AirRemBra4 (and Bra10) are not pulling in data from the correct data points. Instead of using the last 26 data point, they are currently beginning at the top of the column (currently cells 11 to 36 is their respective columns). The formulas are copy and pasted from working offset ranges in the same tab but are reading differently.

2) On the last 6 tabs beginning with Stn Backlog, I want to have defined formulas beginning in cell B119 and continuing downward to at least F200. However, when I add the formulas, the offset reads the formulas as being data and adjusts for that. I do not know how to adjust the dynamic range to ignore formulas in blank cells.

Global Demand-Capacity Management_working (version 3).xlsm

View 6 Replies
View Related
Jun 10, 2013

I want to find the total number of times the number 1 appears in cells B2 to B33.

In each cell I have codes such as 4919409382a, 5021193035v and so on.

I have tried =COUNTIF(B2:B33,"1") but just get 0 returned.

View 3 Replies
View Related
Jan 30, 2009

I have a range of numbers that are not completely sequential and I'd like to separate them out into their individual numbers. In cell A1 I have displaying "1-30" and then in cell A2 I have "50-72" and A3 "100-105", et cetera. I 'd like to have cell B1 through B30 display 1 through 30 (1 in B1, 2 in B2, 3 in B3...) respectively, and then cell B31 through B53 would have 50 through 72.

I need to create a formula that can dynamically pick up the last number after the "-" so that it can work for any number range of any length. I've tried using left and right but that doesn't help when moving from the 10's digits to 100's digits.

View 14 Replies
View Related
Jul 23, 2013

I have a function which copy pastes data into a sheet based on a filter criteria. It is also pasted in a specific layout(shown below) starting from Cell A1 in the top left corner. Therefore the destination sheet could have a different number of rows with values each time.

What I would like to do is count the sum of 'Values' in the destination sheet, and add a total below it.

For example, the [=TOTAL] cell is where I want the total to appear. Below the answer would be 26. But say if Peter wasnt in the record, the formula should still work in identifying the Total as 20.

Title:

Sheridan

Owner

Petrov G

[Code]....

Is there a way I can achieve the desired result? I figured I had to somehow count from the first record by Peter, to the last one, whichever that may be.

View 2 Replies
View Related
Sep 3, 2007

I have a list of employees that i need to simplify into groups as listed below

Employee No's

1-19

20-50

50-199

200-499

500+

In my list of data (attached) that i receive i currently manually count the employee numbers and then put them into the relevant bands dependant on the employee sizes. Example:

Employee List

4

28

35

46

503

376

2000

Employee No's

1-19 - 1

20-50 - 1

50-199 - 3

200-499 - 1

500+ - 2

View 11 Replies
View Related
Feb 4, 2014

I have a long (~42,000) list of dates and times (some including zeros). I would like to name these ranges and use a formula to find the last entry in each column. I know the lists will never go over 45,000.

The data is in this format:

1/31/14

23:58:00

1/30/14

[Code]....

The data will not always be consecutive, so I imagine the criteria would be something like (if there were such a thing) "ISDATE" or something of the sort.

How would I write a formula to dynamically calculate the row number of the last date or time in the column? (In this case, it would be 6.)

View 9 Replies
View Related
Apr 22, 2009

This may not be the best way to do this, but I don't know Macros or Pivot Tables.

I am looking for a way with formulas to do the following:

Within a workbook the 1st sheet is the data entry.

In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:

Example:

Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.

G2:G2999 in the same sheet is where the data is.

Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?

View 9 Replies
View Related
Dec 21, 2008

I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.

View 5 Replies
View Related
Sep 6, 2013

Is it possible with excel, to automatically insert an employees number if their name is inserted.

Example:

Column A If Smith, John is entered in Column B automatically have his employee number show 5668?

Joe, Mary entered in Column A Column B to show 12345

This would save me a huge amount of time going back and fourth.

View 3 Replies
View Related
Dec 13, 2013

number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.

View 2 Replies
View Related
Oct 31, 2008

I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.

The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.

I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.

as there are 600 names and some can appear up to 4 - 5 times!

View 9 Replies
View Related
Aug 9, 2013

I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.

...................................1/1 ....1/2 ....1/3 ....1/4 ....1/5 ....1/6 ....1/7 ....1/8 ....1/9 ....1/10 ....1/11 ....1/12 ....1/13

Captain America ................................A ..........................T .....................................T

Incredible Hulk.......................................................................................... A

Spider Man ........................................................................................................................ ........A

Iron Man ....................................................T

( I added dots because it wouldn't let me space them out?)

We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.

Any formula to use. Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!

View 4 Replies
View Related
Oct 16, 2009

I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.

View 19 Replies
View Related
May 2, 2007

I'm having a fit with this formula; maybe the gurus will show me what I'm doing wrong. I have a list of area numbers along with dollar amounts that go with these area numbers. I have created a named range named "Areas" and another one named "MO_PD".

At the bottom of the worksheet I am doing a recap that takes the area number and sums all the entries in the MO PD column. This approach has been done before with Thomach's help. I'm trying to mimic it, but it's not working for me.

NOTE - I know that I can get around this using a pivot table, but I would like to use this approach for now. I would at least like to know what I'm doing wrong. Also, I'm not totally sure if I did the dynamic named ranges correctly so that may need to be fixed.

View 9 Replies
View Related
Apr 22, 2009

I use sumproduct to put total qty into the Defect Table. But it take a long time for excel to run the counting process. Problem: From master data sheet, I want to plot the qty into the defect table follow by date occurring and by section

View 2 Replies
View Related