# SUM Of Values In 1 Range Based On Specific Criteria In Another Range

Jan 31, 2009
I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against $ Invoiced and $ Committed (i,e worked but not actually invoiced against yet).

Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column.

This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.

View 9 Replies
ADVERTISEMENT
Mar 25, 2009

I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.

My

Public Function SortRange(rngToSort As Range, valCol As Integer)

Dim Swapper As Variant

Dim i As Integer, _

j As Integer, _

k As Integer

For i = 1 To rngToSort.Rows.Count

For j = 1 To rngToSort.Rows.Count - i

If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then

For k = 1 To rngToSort.Columns.Count

Swapper = rngToSort(j, k)

rngToSort(j, k) = rngToSort(j + 1, k)

rngToSort(j + 1, k) = Swapper

Next k

End If

Next j

Next i

SortRange = rngToSort

End Function

View 9 Replies
View Related
Jul 1, 2014

Looking to have a macro call a subroutine every time it finds a cell meeting specific criteria.

Code in plain english would look like this:

For EACH cell in range A1:BZ500 meeting the following criteria:

Cell value is a date

AND

Cell's date is at least a week or more in the future

AND

Cell background (Fill) = RGB color code: (R:191 G:191 B:191)

DO the following:

Call repeatingsub

View 11 Replies
View Related
Jul 24, 2014

I want to count the number of groups of 3 adjacent cells in a horizontal range (C5:EV5) in which any cell in the group of 3 has a value in it (as opposed to all the cells being blank or containing "0"). For example, if any or all of the cells in C5:E5 have a value in it, it would count as 1, and if any of the cells in the group F5:H5 have a value, it would also count as 1, and so on. Is there a way to use COUNTIF for this?

View 5 Replies
View Related
Jun 3, 2009

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

View 13 Replies
View Related
Jan 9, 2011

I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.

for example

I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.

I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.

Book1.xlsx

View 8 Replies
View Related
Dec 4, 2012

Attached excel sheet below. Suggest a formula to get the count of items that fall in a specific data range ?

Count of items that specify a range criteria.xls

View 4 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
Feb 12, 2014

Is it possible to utilize the AVERAGEIF function for a specific range of values. For Example, instead of ">45", I would be looking to only average the cells that have values between 40 & 60. I've tried several different ways to input that into the function but nothing seems to work.

View 3 Replies
View Related
Sep 30, 2009

I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.

Table is as follows

Date value(a) Value (b) ....

1/1/09 10 8

2/1/09 8 6

3/1/09 5 2

View 4 Replies
View Related
Jul 16, 2014

I need to sum a specific range of values between two headers in several dynamic sheets. The values in the sheets always starts in row 10 and column H (Forcast). For example the header could be "Sales" in column A. Then I want to sum values above the header "Sales" in forcast column H. The next header could be "Operations", where I want to sum the values between "Sales" and "Operations" in column H. Next header could be "Rents" then I want to sum the values between "Operations" and "Rents" in column H, and this is how it continues... Is there any macros for this?

View 2 Replies
View Related
Oct 24, 2007

I need to keep track of tardy occurences, but I only have to consider occurrences that have occurred in the past six months from the current day. Column A contains the names of the employees, Column C thru IV contains daily dates beginning with 1/1/2007. Column B contains a CountIf function to count the number of times T appears in columns c - iv.

View 9 Replies
View Related
Mar 1, 2013

Is there a simple way to include the values of an entire range?

This is what I tried, but I get #name

Code:

=SUMPRODUCT((Datasheet!J2:J65000 = Range("C3:AA3"))*Datasheet!F2:F65000)

If I use a single cell it works, but only with what matches that cell.

Code:

=SUMPRODUCT((Datasheet!J2:J65000 = C3)*Datasheet!F2:F65000)

I'd like the criteria to match anything in that range C3:AA3. Do I need to specify each cell individually or is there an easy way have it use the entire range's values for its criteria?

View 9 Replies
View Related
Jul 17, 2009

Please see the attached .xls.

I've tried to replicate my formula (result is in cell J5) via an array formula but keep getting stuck.

Basically there is a 2 dimensional array of weights (range1, limits)

each range in range1 and "limits" has a midpoint

a constant value (Fixed Amt) is added to each midpoint in range1

then multiplied by another constant value (Inflation Index)

The value is then capped by corresponding value in the limits range

this is repeated for all points in the 2 dimensional array (range1,limits) (essentially weighting the results)

and the sum is returned.

I've been able to get most of the way with an array formula, but end up collapsing the array when I use the min function.

View 6 Replies
View Related
Dec 28, 2007

I need to sum values from the same column, in different rows, but is there a way to sum each value based on the name in the first cell of the row. Since I realize I probably did not word that well, I will give an example. The data would be as follows

Bellcrank 3

Bellcrank 5

Gearbox 2

Bellcrank 6

Is there a way I could just sum the Bellcrank values? I understand that i could just use the sum function in this case, but I want the sum to automatically update if another bellcrank row is added.

View 8 Replies
View Related
Nov 26, 2009

I have a problem with a calculation i am trying to do. I have to calculate holiday allocation for staff but i my company also offers length of service bonus days. After 5 years you get 1 extra day, after 10 years you get 2, after 15 you get 3 and so on. In my sheet i have a formula based on their start date that tells me their length of service in the following format: 10 years, 2 months.

I have put an IF Statement in for each of the different milestone criteria but the result is not coming back so i know i am missing something. Is there something wrong with the formula itself or is there a better one that will allow me to put the start and end of the range i want it to look at? For example can i tell it to say if between 5 years and 9 years 11 months put 1, if between 10 years and 14 years 11 months put 2 etc in column H.

View 3 Replies
View Related
Aug 19, 2008

I am looking for VB code that will format a range of cells from a number format to a percentage format based on a reference cell. The reference cell is a drop down created from a list validation.

For example: If the reference cell (cell A1) =1 then cell range B2:C4 would be a number format. If A1 = 2 then cell range B2:C4 would be a percentage format.

I have searched for hours trying to find a relevant thread; I know they are out there, maybe I just wasn't typing in the correct search words.

View 5 Replies
View Related
Mar 18, 2009

Trying to sum a range based on the criteria of the 1st position within each cell in the range. Cells have data like "A-8", "A-4", for a total of 12 for "A". This is the formula I have that is not working.

=SUMIF(left(C4:P4,1),"A",RIGHT(C4:P4,1))

View 6 Replies
View Related
Sep 25, 2013

I would like Excel to return me a value based on many criterias in the same worksheet.

E.g

A

B

C

[Code].....

QN: For any of the value from Col B to Col N, i want the result of Column A appearing in my cell.

View 1 Replies
View Related
Sep 27, 2013

I have the following data

Country

Aug-13

Sep-13

Oct-13

Nov-13

UK

3

5

3

2

USA

10

14

16

13

France

1

5

7

9

What I would like to do is sum a specific range of this data based on three criteria. Each criteria is in its own cell.

- Start Date

- End Date

- Country

So if I wanted to sum from 'Sep-13' to 'Nov-13' for 'USA', the formula would give me the answer 43.

View 4 Replies
View Related
Jan 3, 2014

Need to select a range from colum A7 to E20.

Condition.

1) if the last colum (E20) should be selected if in the colum A equal to "Thank You".

View 2 Replies
View Related
Feb 15, 2010

I am copying from cell A2 to C2, then A8 to C8, etc. if the cell in column "A" contains the text "Item" until the end of data in column A. Here is what I am attempting:

Sub MoveItem()

sText = "Item"

sText = LCase(sText)

iMax = Cells.SpecialCells(xlCellTypeLastCell).Row

For i1 = iMax To 1 Step -1

If InStr(1, LCase(Cells(i1, 1)), sText) <> 0 Then

Cells(i1, 1).Copy Destination:=Cells(i1, 3)

End If

Next i1

End Sub

The problem is the destination location. If I define it as a Range eg: Cells(i1, 1).Copy Destination:=Range("C2") The macro works, but does not iterate the destination cell.

View 3 Replies
View Related
Apr 30, 2009

I'm trying to figure out how to count by two criteria, only one of the criteria has a value among multiple values in a cell and the other is a range. I tried to use sum but it doesn't count the value and count doesn't recognize the range. I attached the file so you can see what we're trying to do.

View 4 Replies
View Related
May 7, 2009

I am trying to copy a range of cells, based on a set of criteria that changes constantly.

In Column A, I have numerical values that are present every 10th cell. In columns B through D, I have various text data that corresponds to the number in Column A. So for example, in A50, I have the number 46975. In the range B50-D59, I have text data that goes with the number in A50.

Each day I run a few calculations and based on the number I get, I want to find the exact match in column A, then copy all the data in columns B through D that are related. So if my calculations result in 46975, I want to automatically copy over to another worksheet the range B50-D59.

View 4 Replies
View Related
Jan 22, 2009

I have a table of data with which I need a macro to clear a range of data based on certain criteria. I have attached a file as an example. In this example the current period is 6. I therefore need to clear columns which have a period of 7 or higher.

Essentially, if the current period is x, then clear the range in the period columns if the period is greater than x.

View 6 Replies
View Related
Jul 14, 2006

which I have modified from one that I use before for sheets 1 & 3 (instead of 4&5)

But it isn't working! I have defined a dynamic range in sheet 4, which is the range I want to search (its named "search") should i be using this range in the formula somewhere?

Private Sub CommandButton1_Click()

Dim rngFind As Range

Dim strFirstAddress As String

With Sheet4.UsedRange

Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues)

If Not rngFind Is Nothing Then

strFirstAddress = rngFind.Address

Do

rngFind.EntireRow.Copy Sheet5.Range("A" & Sheet5.Rows.Count).End(xlUp).Offset(0, 0)

Set rngFind = .FindNext(rngFind)

Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress

End If ..................

View 9 Replies
View Related
Feb 18, 2008

I have 2 columns on column contains 2 numbers, the other comlumn has names. We'll use A1:A4 and B1:B4 for the example.

Row A Row B

25 John Smith

50 Mike Jones

25 Chris Carter

50 Mike Jones

I want to be able to count one number in "A" but only if the name is not duplicated in "B".

My result for #25 would be 2.

My result for #50 would be 1, because Mike Jones is duplicated.

The results would be displayed in two seperate boxes, so I would only need on formula to count #25, then one formula to count #50.

View 3 Replies
View Related
Mar 19, 2008

Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY.

this is what I have come up with....

sumif(N10:N49, "Business", AW10:AY49)

but it returns incorrect values and not the whole range????

Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do...

H2 and H3 are my attempted formulas

View 3 Replies
View Related
Sep 22, 2009

I need to populated a specific range of cells based on a value of any kind in a header cell. Attached is a sample of the end results. Please remember each header cells needs to determine what happens the range of cells below the header cells.

View 3 Replies
View Related
Aug 31, 2012

I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.

View 4 Replies
View Related