Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

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)

I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.

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.

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.

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.

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

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.

I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).

I am looking to count every 4th column that has a average score less than 90. My columns are set with 3 scores and 4th column has the average of those scores. Like such:

untitled.jpg

I can count them using a SUMPRODUCT(--(MOD(COLUMN function but I cannot get a criteria to work.

Attached is a workbook that better explains what I am trying to do. I am counting pastdue accounts from several different insurance companys. I have several pastdue brackets, and I need the total amount of pastdue accounts for each bracket, for each insurance company. I think the countifs function wrapped in an index match may get the job done, but I have not been able to figure it out. right now I have a countifs wrapped in an if statement, and it is producing incorrect results.

I am trying to auto-fill a column based on certain criteria - in other words, fill in blank cells based on a cell above, just a little more complex version. I have added an attachment to give everyone a visual of what I am describing and then maybe this explanation will make a little more sense.

First, if you open the attachment, the left hand side is a small example of what I have. The middle is the condition. And the right hand side is what I'd like to see happene.

In the example, I am matching up a time and when I hit that time, I am adding on a certain number of miles per hours based on the condition and the cell above. Meanwhile, I am filling in the blank cells with the condition until the condition changes. So a cell is incremented based on the condition and gets its value from the cell above.

I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)

A B C D E F G H I

1 Descriptor Group Identifier Inventory Total Vaue Invetory Type

Group Total Incomplete Inventory Value

[Code] .......

Here is what I would like in:

The sum of the total value of half finished and unfinished inventory for each group.

So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)

I think an array formula would do this, but I'm not really sure how this would work.

I have a sheet with 2 columns. First Column lists sizes of Devices. Second colum lists a servername. Example:

A B 30 server_a 30 server_a 65 server_a 65 server_a 45 server_b 45 server_b 15 server_b

What I want to do is a device count, not device sum on a criteria based on Column B.

For instance whenever column B equals servername A, then count how many devices it has.

This would equal 4 instead of 190 which is the sum of the devices. I can do a sumif statement, very easily, but I don't want a sum, I just want a count.

There is probably a simple way of doing this in excel but the only way that i can think of doing it is through VBA.

There are about 25 columns and 4000 rows in my spreadsheet. I want my macro to look up 5 criteria in Sheet 1 and the same 5 criteria in the previous years spreadsheet. If all 5 values in Sheet 1 match all 5 values in Sheet 2, i want it to import a cell's value from that row in Sheet 2 to the active row in Sheet 1.

Here is the code I have so far:

Code:

Sub Import2012Numbers() Dim BlockNbr As String Dim Position As String Dim Scenario As String Dim LOA As String Dim ScenVal Dim x As Integer

[Code] ...

I think i've got everything down in terms of the variable definitions etc, but i think the problem is coming from the order or method of the for each and looping process.

I need the macro to take A2, S2, U2, W2, and V2 in Sheet 1 and look through all the rows in Sheet 2 to see if any of the rows match all 5 criteria. Only then do i want it to move on to A3, S3, U3, W3 AND V3 in Sheet 1

When i step through the code i keep getting the error message "Next without for" for i, or "For command already in use".

I've worked on this one way to long and thought I'd ask here now.

Column A Column B A 1 A 0 A 0 B Blank Cell B 0 B Blank Cell B 0 B 0

I have a third cell that I would like to count how many zeros in Column B there are that correspond to the letter B in Column A. Blank cells cannot be counted. This answer should be 3.

I am really struggling to summarise weekly budget data into period budget data by subcategory.

I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.

I am trying to get the average of two columns column A and Column B Column A has text (survey type) i.e. Move In, Mid Year, Year End Column B has the scores given on that survey type i.e. 70 I am trying to get the average of of all the Move In cells from column A for each survey type so that I get average of all the scores in column B.

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.

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.

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