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

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

## SUM X Column Range Based On Criteria In Cell

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

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

## Macro For AverageIFS With Multiple Criteria In Same Criteria Range

May 18, 2014

Macro for AverageIFS, with multiple criteria in the same criteria range....

## Multiple Criteria SUMIF Using Column As Negative Criteria?

Mar 24, 2014

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.

## Sum Range Based On Criteria Of Another

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.

## Sum Column Based On 2 Criteria

Oct 13, 2011

VBA code to summarise a column based on 2 other columns.

All flagged codes must be summed and all non flagged codes summed.

code.....amount.....flag.......summerised code..sum amount...flag
aaa100...yaaa200...ybbb100...ybbb200...yaaa100...yccc100...ybbb100...yaaa300ccc100...ybbb300
aaa100ccc200aaa100bbb100bbb100ccc100ccc100aaa100bbb100

## Sum Range Based On Text Criteria

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

## Return Value Based On Range Of Criteria?

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.

## Sum Range Of Cells Based On Criteria?

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.

## Select Range Based On Criteria?

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

## Copy Every Nth Range Based On Criteria

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.

## Return Column Header Based On Column Criteria And Number Value

Feb 7, 2014

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

Here's data table:

ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00

CriteriaNumberValue

## Count Of Cells In Column Based On Criteria On That Line In Different Column?

Mar 10, 2014

I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.

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

## Looking At Criteria In One Column And Creating List That Fit That Criteria

Aug 25, 2014

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

Sheet 1 (order)
A
052611
052806
052843

Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N

how I can write is in a formula?

## COUNTIF: 2 Criteria: Date Range Column & Text Column

Dec 8, 2005

Col C = Text and Col F = dates
I would like to count the # of times a value occurs in Col C based on a date
range in Col F.

## Count Every 4th Column Based On Criteria?

Oct 31, 2013

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.

Non-VBA preferred

## Count From One Column Based Off Of Criteria From Another

Dec 6, 2013

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.

test book 1.xlsx‎

## Column Title Based On Criteria

Oct 18, 2008

I would like to title columns based on values from other cells...

## Filling In A Column Based On Certain Criteria

Mar 5, 2008

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.

## Sum Column Based On Two Criteria From Two Columns

Nov 7, 2012

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.

## COUNTA IF Criteria Based On 2nd Column

Nov 12, 2012

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.

## VBA Import Column Based On 5 Criteria

Aug 19, 2013

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

## Add Zeros Based On Another Column Criteria

Sep 1, 2013

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.

## Sumif Based On 2 Row And 1 Column Criteria

Apr 18, 2014

A1:A10 = Criteria Range 1
B1:B10 = Criteria Range 2
C1:E1 = Criteria Range 3

How to sumif all 3 criteria are met?

## Sum Cells Based On Row And Column Criteria

Apr 30, 2014

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.

## Average Column Based On Criteria Of Another

Nov 30, 2007

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.

## Copy Range Based On Criteria That Changes Constantly

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.

## Macro To Delete A Range Based On Certain Criteria

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.

## Copy Rows In Range Based On Criteria

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

With Sheet4.UsedRange
Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues)
If Not rngFind Is Nothing Then
Do
rngFind.EntireRow.Copy Sheet5.Range("A" & Sheet5.Rows.Count).End(xlUp).Offset(0, 0)
Set rngFind = .FindNext(rngFind)
End If ..................