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.

Rota - Add Up The Total Number Of Hours Per Week Per Employee

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.

Sumproduct- To Get The Total Number Of Columns That Have A Specific Match-up

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? ....

Sumproduct With Dynamic Range

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.

Incorrect Sumproduct Array: Sum Up The Total Number Of Hours Consumed On A Particular Activity Within A Specified Period

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.

RANKIF Using SUMPRODUCT With Dynamic Range

Jul 16, 2012

I have a "RANKIF" formula using SUMPRODUCT:

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

Sumproduct With Dynamic Range Across Multiple Workbooks

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.

INDIRECT Not Working For Dynamic Named Range In SUMPRODUCT

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]

To Find A Total Number Of Rows To A Blank Cell

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.

Formula To Add Total Hours Employee Worked For Each Day?

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] .....

Missing Blank In Dynamic Range

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.

VBA To Remove Rows That Contain 0 Or Blank In Dynamic Range?

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.

Dynamic Range To Look Past Blank Cells

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.

VBA Dynamic Range Till First Blank But Cells Contain Formula?

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?

Formula For Total Cost Based On List Of Hours For Each Employee Times A Table Of Rates

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‎

Adjust Dynamic Range To Ignore Formulas In Blank Cells?

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

Find Total Number Of Certain Character In Range On Cells

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.

How To Separate Out A Dynamic Number Range

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.

Count Number Of Cells In Dynamic Range In VBA?

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.

Employee Number

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

Row Number Of Last Date / Time In Column (Dynamic Range)

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.)

Macro Allow To Total The Data On The Total Sheet Depending On What Unit Number Is Selected

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?

Add Each Day's Total While Displaying A Positive Number If The Total Exceeds 6000

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.

Excel Automatically Insert Employee Number

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.

Formula To Multiply Total Number Per Category With The % Of Total?

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.

Allocating Employee Names With Unique Reference Number

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!

Counting Number Of Days For Each Employee Since Last Day Of Absence / Tardy

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!

Formula- Calculate The Number Of Days Since An Employee Worked

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.

Dynamic Ranges And Sumproduct

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.

Sumproduct Double Criteria: Put Total Qty Into The Defect Table

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