Sum Values Based On Multiple Criteria (comma Delimited) In A Single Cell 


Sum Values Based On Multiple Criteria (comma Delimited) In A Single Cell  Excel 
View Answers 
Hello all...
I'm trying to find out if there is a function that will allow me to sum the values for a designated set of critieria that may have one or more values in a cell.
Attached is a spreadsheet that contains two worksheets, [data] and [target].
The [data] tab contains the data set to be used with two columns, (code) and (balance).
The [target] tab contains the results of my "query" with three columns, (code set), (month), and (value).
In the [target] tab, the (code set) column contains the criteria I want to "filter" the data set by. sometimes there will only be one criteria identified. But other times, there may be two or more criteria identified and separated by commas.
In the [target] tab, the (value) column and first two rows, I've used the SUMIF function where if the "one" identified criteria is found it will total the sum.
However, for the last row, how do I get the SUMIF function to recognize that there are multiple criteria values that need to be found then summed?
Any guidance would be greatly appreciated.
ML
I'm trying to find out if there is a function that will allow me to sum the values for a designated set of critieria that may have one or more values in a cell.
Attached is a spreadsheet that contains two worksheets, [data] and [target].
The [data] tab contains the data set to be used with two columns, (code) and (balance).
The [target] tab contains the results of my "query" with three columns, (code set), (month), and (value).
In the [target] tab, the (code set) column contains the criteria I want to "filter" the data set by. sometimes there will only be one criteria identified. But other times, there may be two or more criteria identified and separated by commas.
In the [target] tab, the (value) column and first two rows, I've used the SUMIF function where if the "one" identified criteria is found it will total the sum.
However, for the last row, how do I get the SUMIF function to recognize that there are multiple criteria values that need to be found then summed?
Any guidance would be greatly appreciated.
ML
Similar Excel Tutorials
Split Text into Multiple Cells in Excel
How to split text from one cell into multiple cells quickly and easily in Excel. This includes how to split names, ...
How to split text from one cell into multiple cells quickly and easily in Excel. This includes how to split names, ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file). This allows you to turn any Excel spread ...
How to export an Excel file to a CSV file (comma separated values file). This allows you to turn any Excel spread ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Complex Structured References (Table Formulas) in Excel
How to use complex structured references, table formulas, in Excel. If you don't already understand how structured ...
How to use complex structured references, table formulas, in Excel. If you don't already understand how structured ...
Helpful Excel Macros
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel  AutoFilter
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field  AutoFilter
 This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t
 This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t
Display the AutoFilter Criteria Applied to a Data Set in a Cell in Excel  UDF
 This free Excel UDF (user defined function) allows you display the filter criteria that has been applied to a data set i
 This free Excel UDF (user defined function) allows you display the filter criteria that has been applied to a data set i
Delete Entire Rows Based on Predefined Criteria (Text)
 This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
 This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Excel Macro that Searches Entire Workbook and Returns All Matches
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Similar Topics
This question is a bit different from any other that I could find. For important reasons I have a spreadsheet that has a setup similar to the example below. I would like, for example, to use a formula that would sum the total "Criteria 2" for those columns labeled with "A". I would expect the total to be equal to 40.00, but I cannot figure out a way to have a formula sum all criteria 2s that match a description found in multiple columns (there is a subheader below the column headings that I need to leave intact  otherwise I would just sum the data into one column). Thanks in advance!
The column headers are A, B, C, A, D, E, F and are over the 10.00's  cannot figure out how to make it show up correctly in this post. I've also attached a sample workbook.
A B C A D E F
Criteria 1 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 2 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 3 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 1 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 5 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 2 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 7 10.00 10.00 10.00 10.00 10.00 10.00 10.00
The column headers are A, B, C, A, D, E, F and are over the 10.00's  cannot figure out how to make it show up correctly in this post. I've also attached a sample workbook.
A B C A D E F
Criteria 1 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 2 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 3 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 1 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 5 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 2 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Criteria 7 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Hi I've searched around and am having a tough time solving this issue. In simple terms, I want to use the sumif function using multiple criteria from multiple columns. Here's a simple example:
A1: 1 B1: 33 C1: 1
A2: 2 B2: 1 C2: 1
A3: 1 B3: 4 C3: 1
A4: 1 B4: 9 C4: 2
I want to sum the values in column B when both A and C have the value of 1. I have tried the below formula, but it does not accept criteria from multiple columns:
=SUM(IF((A1:A4="1")+(C1:C4="1"),B1:B4,0))
Any suggestions or advice would be helpful.
A1: 1 B1: 33 C1: 1
A2: 2 B2: 1 C2: 1
A3: 1 B3: 4 C3: 1
A4: 1 B4: 9 C4: 2
I want to sum the values in column B when both A and C have the value of 1. I have tried the below formula, but it does not accept criteria from multiple columns:
=SUM(IF((A1:A4="1")+(C1:C4="1"),B1:B4,0))
Any suggestions or advice would be helpful.
I want to use SUMIF to add numbers together in a row only if they are between 65 and 90, inclusive. I was able to use SUMIF to add numbers in the row >=95, but the formula didn't work using "AND(>=65,<=90)" and seems to want to use only a single value. There must be a way to specify a range of values in the criteria portion..?!
Here is an example of the SUMIF formula that worked for >=95:
=ABS(ROUNDDOWN(1.5*(SUMIF($B$3:$D$3,">=95",$B$3:$D$3)+SUMIF($I$3,">=95",$I$3)+SUMIF($L$3:$N$3,">=95",$L$3:$N$3))+1.25*(SUMIF($E$3:$F$3,">=95",$E$3:$F$3)+SUMIF($J$3,">=95",$J$3)+SUMIF($O$3:$P$3,">=95",$O$3:$P$3))+(SUMIF($G$3:$H$3,">=95",$G$3:$H$3)+SUMIF($K$3,">=95",$K$3)+SUMIF($Q$3:$R$3,">=95",$Q$3:$R$3)),0))
Since substituting "AND(>=65,<=95)" didn't work, and the possible values are in increments of 5, I specified each possible value in a separate SUMIF statement (e.g. "65","70","75", "80","85", and "90") and that works, but it will make my formulas extremely long. As you can see I have 3 different sections I want multiplied by different amounts, and there are several ranges within each of those sections.
Any suggestions would be most helpful!
Hey Everyone.
I downloaded an Advanced Filter Macro from Contextures.com (http://www.contextures.com/AdvFilterSearchWord.zip) and it works great.
I was wondering if anyone can help me set more criteria.
On the Worksheet it has the code
=AND(OR($B$1="",D7=$B$1),ISNUMBER(SEARCH($A$1,B7)))
On the VBA Worksheet Code Is This:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Or _
Target.Address = "B$1" Then _
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"), Unique:=False
End If
End Sub
How can I add more Criterea to search more columns? Thanks Guys
I downloaded an Advanced Filter Macro from Contextures.com (http://www.contextures.com/AdvFilterSearchWord.zip) and it works great.
I was wondering if anyone can help me set more criteria.
On the Worksheet it has the code
=AND(OR($B$1="",D7=$B$1),ISNUMBER(SEARCH($A$1,B7)))
On the VBA Worksheet Code Is This:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Or _
Target.Address = "B$1" Then _
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"), Unique:=False
End If
End Sub
How can I add more Criterea to search more columns? Thanks Guys
In a nutshell, I want to do a SUMIF() but with multiple seperate ranges, single criteria.
If it's a single range, SUMIF() works exactly the way I'd expect. That is unfortunately not the case here.
Thus far I've tried a couple of approaches:
Seperating ranges, with parens and commas:
=SUMIF((B1,N1,Z1),">0") Gave a value error
Assigning B1, N1 and Z1 to the named range 'values':
=SUMIF(values,">0") Also gave a value error
It's looking to me that SUMIF() simply doesn't handle adding up separate ranges.
Perhaps there is another approach? Is my syntax incorrect?
Any help would be awesome.
If it's a single range, SUMIF() works exactly the way I'd expect. That is unfortunately not the case here.
Thus far I've tried a couple of approaches:
Seperating ranges, with parens and commas:
=SUMIF((B1,N1,Z1),">0") Gave a value error
Assigning B1, N1 and Z1 to the named range 'values':
=SUMIF(values,">0") Also gave a value error
It's looking to me that SUMIF() simply doesn't handle adding up separate ranges.
Perhaps there is another approach? Is my syntax incorrect?
Any help would be awesome.
I need to create an array formula with 3 criteria. I am using sumif but it seems to add on more than the selected criteria. and sumproduct does not seem to give me any values.
I have the a data sheets with, the date in col A, Values in col K and detartments in Col I
I would like to do a month by month dept by dept table.
this is the formular i tried using:
=IF(AND(SUMIF$K$5:$K$57,$B4,$I$5:$I$57),SUMIF$A$5:$A$57,C$4,$I$5:$I$57))),(SUMIF($K$5:$K$57,$B4,$I$5:$I$57)),0)
With B4 being the department code and C4 deing the Month.
It works untill there is 2 months with data in the data page, then the values show in both months.
Any assistance would be greatly appricated.
Cheers
I have the a data sheets with, the date in col A, Values in col K and detartments in Col I
I would like to do a month by month dept by dept table.
this is the formular i tried using:
=IF(AND(SUMIF$K$5:$K$57,$B4,$I$5:$I$57),SUMIF$A$5:$A$57,C$4,$I$5:$I$57))),(SUMIF($K$5:$K$57,$B4,$I$5:$I$57)),0)
With B4 being the department code and C4 deing the Month.
It works untill there is 2 months with data in the data page, then the values show in both months.
Any assistance would be greatly appricated.
Cheers
Solved: 9/4/2013
I figured this formula out a few days ago before I posted, but had some errors in my data and did not realize it was correct.
If anyone sees an issue with it let me know... but I have tested it extensively on a very large array of 100 rows by 24 wide.
I attached a sample XLS with the solution!!!!
Thanks!
Original Post he
I need a single array formula (one cell) to do this...
I have a worksheet where I need to find the MINimum COLUMN number from a range or rows/columns whe
#1) Only look at rows in the first range (A1:A4) where the first column "A" has a specific value (e.g., "HI"). Note that this may result in MULTIPLE rows applicable from the criteria Column "A".
#2) For rows matching from #1; find the first (leftmost) column in the range that has a numeric value > 0 in any cell (again, only for rows matching criteria).
I had a lot of permutations on this...only to realize that I am not getting there quickly. It must be a single formula and not VBA due to company policy.
general example
A B C D E F
1 xx 0 3 4 2 0
2 HI 0 8 0 0 0
3 yy 7 5 4 1 1
4 HI 0 0 1 0 1
So in the above example:
#1) My criteria is ALL rows having "HI" in column A. This is then rows #2 and #4 are to be inspected.
#2) ONLY for rows with "HI" (#2 and #4), look at values in all cells in range B1:F4 and give me the minimum column # where the value is > 0. For this example, with "HI" as the criteria, the formula should return column 3 ("C") since cell C2 is the first nonzero matching the >0 criteria for rows 2 & 4.
IMAGE of my current usage (but WITHOUt COL A criteria) is attached.
Screen Shot 20130903 at 8.34.54 PM.png
Thanks!
vfrost
Code:
This above code breaks when i attempt to select criteria on two of the ranges like if i put something in C that date appears in B which is fine but then as soon as i put something in M on the same row it locks up the sheet and the code breaks.
Probably just incorrect use of End If or something.
Thanks,
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A5:AJ" & Rows.Count)) Is Nothing Then Exit Sub If Range("C" & Target.Row) <> "" Then Range("B" & Target.Row) = Date End If If Range("M" & Target.Row) <> "" Then Range("N" & Target.Row) = Date End If If Range("O" & Target.Row) <> "" Then Range("P" & Target.Row) = Date End If If Range("Q" & Target.Row) <> "" Then Range("R" & Target.Row) = Date End If If Range("T" & Target.Row) <> "" Then Range("U" & Target.Row) = Date End If If Range("AA" & Target.Row) <> "" Then Range("Z" & Target.Row) = Date End If End Sub
This above code breaks when i attempt to select criteria on two of the ranges like if i put something in C that date appears in B which is fine but then as soon as i put something in M on the same row it locks up the sheet and the code breaks.
Probably just incorrect use of End If or something.
Thanks,
This is my first foray into Ozgrid, so please forgive any inefficiencies I may have in presenting my problem. I’ll be happy to clarify further if needed and/or post sample files on request.
I’m relatively new at using the INDIRECT function, and am having a hard time setting up the syntax for ranges, and even knowing if those ranges will work.
I have a workbook with multiple sheets (let’s call them Program sheets) created from a template that contains variable numeric data that I need to sum by creating a formula on a Summary sheet within the same workbook. The criteria for IDing and summing the data from the Program sheets is spread over 3 cells in adjacent columns (let’s call them $E7, $F7 and $J7) on the Program sheets. A string concatenation of these cells will not create a unique string value on any one sheet as there are potentially multiple rows of data on each sheet and across sheets that could have the same value string. The Summary sheet is a report that contains hardcoded values in adjacent cells ($C4, $D4 and $E4) that will match values found in columns E, F and J from the Program sheets.
I’d like to have the formula sum all values within the range P7:AA70 across all the Program sheets when the entries into E, F and J cells (from Program sheets) match $C4, $D4, and $E4 cells on the Summary sheet, keeping in mind that there could be multiple instances of the same values over several rows within the Program sheets (that’s OK, because I want each instance to be part of the sum)
Here’s a formula I created for summing values found in a range based on a single matching criteria across sheets. Can this be adapted to the new sum formula I need?
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_List&"'!G7:G70"),'ByMonth Summary'!$G7,INDIRECT("'"&Sheet_List&"'!P7:P70")))
Sheet_List is a named range on a separate tab that lists the names of the Program sheets that I need to sum from.
P7:P70 is the range that the sumable data lies in.
G7:G70 is the range that contains values that need to match the criteria on the ByMonth Summary sheet cell G7.
For the new formula, I no longer want to sum based on criteria in the G column, but rather on criteria in the multiple columns I outlined in my diatribe above.
I’m relatively new at using the INDIRECT function, and am having a hard time setting up the syntax for ranges, and even knowing if those ranges will work.
I have a workbook with multiple sheets (let’s call them Program sheets) created from a template that contains variable numeric data that I need to sum by creating a formula on a Summary sheet within the same workbook. The criteria for IDing and summing the data from the Program sheets is spread over 3 cells in adjacent columns (let’s call them $E7, $F7 and $J7) on the Program sheets. A string concatenation of these cells will not create a unique string value on any one sheet as there are potentially multiple rows of data on each sheet and across sheets that could have the same value string. The Summary sheet is a report that contains hardcoded values in adjacent cells ($C4, $D4 and $E4) that will match values found in columns E, F and J from the Program sheets.
I’d like to have the formula sum all values within the range P7:AA70 across all the Program sheets when the entries into E, F and J cells (from Program sheets) match $C4, $D4, and $E4 cells on the Summary sheet, keeping in mind that there could be multiple instances of the same values over several rows within the Program sheets (that’s OK, because I want each instance to be part of the sum)
Here’s a formula I created for summing values found in a range based on a single matching criteria across sheets. Can this be adapted to the new sum formula I need?
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_List&"'!G7:G70"),'ByMonth Summary'!$G7,INDIRECT("'"&Sheet_List&"'!P7:P70")))
Sheet_List is a named range on a separate tab that lists the names of the Program sheets that I need to sum from.
P7:P70 is the range that the sumable data lies in.
G7:G70 is the range that contains values that need to match the criteria on the ByMonth Summary sheet cell G7.
For the new formula, I no longer want to sum based on criteria in the G column, but rather on criteria in the multiple columns I outlined in my diatribe above.
Hello,
I am struggling with a method to do a Vlookup via VBA, but to match a row with 2 criteria, instead of the typical 1 criteria.
My VBA function is going to do something like this..
For r = 2 to 25
For c = 6 to 10
ws.cells(r,c).value = ... Here is where I need help. I need the value to = Row X, Col 8 from another worksheet. I need to determine Row X based on 2 criteria, whats in Col 1 and Col 4 for example. The criteria values I am trying to match will be identified as offset cells from the source. So the first criteria I need to match will be offset(0,4) from (r,c), and criteria 2 I want to match will be offset(1,c) from (r,c) in my Loop.
I hope this makes sense. Essentially I am saying I need to identify which row on Sheet 2 has the text string "John" in Column A, and "Friday" in Column E, and I want to return the value in Column G from that row. There will only be 1 row that matches both criteria, but there will be several matches for either criteria in their respective columns.
I am struggling with a method to do a Vlookup via VBA, but to match a row with 2 criteria, instead of the typical 1 criteria.
My VBA function is going to do something like this..
For r = 2 to 25
For c = 6 to 10
ws.cells(r,c).value = ... Here is where I need help. I need the value to = Row X, Col 8 from another worksheet. I need to determine Row X based on 2 criteria, whats in Col 1 and Col 4 for example. The criteria values I am trying to match will be identified as offset cells from the source. So the first criteria I need to match will be offset(0,4) from (r,c), and criteria 2 I want to match will be offset(1,c) from (r,c) in my Loop.
I hope this makes sense. Essentially I am saying I need to identify which row on Sheet 2 has the text string "John" in Column A, and "Friday" in Column E, and I want to return the value in Column G from that row. There will only be 1 row that matches both criteria, but there will be several matches for either criteria in their respective columns.
Hi,
I have searched the forums to see if there is a similar prob already answered but to no avail (apologies if this has already been answered).
I am designing a sheet for a Uni project that needs to calculate the average attendance rates for students between age groups. My data has the age in a specific age for example:
Age
18
19
19
21
etc.
I am trying a SUMIF function that adds all attendance rates based on a criteria  however I can't get the criteria to fall between two ages ranges (more specifically, I need to sum the averages where the ages is between 21  25), as the function seems to only allow for one expression of criteria.
My function currently Reads:
=SUMIF(A1:A25, ">=21 & <=25", B1:B25)
Where A is the column recording ages and B is the column recording attendance rates. It presently returns 0.
Is there a better function for this task, or a way around the SUMIF criteria?
Any help would be greatly appreciated!
I have searched the forums to see if there is a similar prob already answered but to no avail (apologies if this has already been answered).
I am designing a sheet for a Uni project that needs to calculate the average attendance rates for students between age groups. My data has the age in a specific age for example:
Age
18
19
19
21
etc.
I am trying a SUMIF function that adds all attendance rates based on a criteria  however I can't get the criteria to fall between two ages ranges (more specifically, I need to sum the averages where the ages is between 21  25), as the function seems to only allow for one expression of criteria.
My function currently Reads:
=SUMIF(A1:A25, ">=21 & <=25", B1:B25)
Where A is the column recording ages and B is the column recording attendance rates. It presently returns 0.
Is there a better function for this task, or a way around the SUMIF criteria?
Any help would be greatly appreciated!
I have a spreadsheet with 7000 lines of data . I want to delete certain rows of data that meet criteria. I need to filter column V to see only a criteria of "NEP". I then want to filter column P to see only a criteria of "Groom". I then want to filter column G to see only a criteria of "ONSP*". Lastly, I will then delete all rows that meet the criteria of "ONSP*" in that column G. My code runs the first "filter" just fine, but does not move past the 2nd "filter" command. I'm missing something key about having multiple filters using VBA code language. Can you help ?
Thanks,
Greg
Here's my code:
======================================
Please Login or Register to view this content.
=============================
Hello!
I received assistance in an earlier post that helped tremendously, but I'm
having an additional problem with the code I received.
Situation: I import a 3 column list into a worksheet in Excel. On several
other sheets, I run a VLOOKUP code in VBA to autoenter data. When certain
criteria is entered in cell A1, VLOOKUP enters the corresponding results
into the same row into B1 and C1. I'm using the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As Variant
If Target.Address = "$A$1" Then
Set rng = Worksheets("Sheet2").Range("A1:C1800")
res = Application.VLookup(Target, rng, 2, False)
If IsError(res) Then
Range("B1:C1").Value = "Manual Entry Required"
Else
Range("B1").Value = res
Range("C1").Value = Application.VLookup(Target, _
rng, 3, False)
End If
End If
End Sub
This code only targets cell A1 on the current worksheet. I need it to
target about 100 rows of column A so if the data entered in cell A55 is
different than the data entered in cell A1, it returns the proper info for
data entered in A55.
I've tried using several Target.Range codes, to no avail.
Any help would greatly be appreciated.
I received assistance in an earlier post that helped tremendously, but I'm
having an additional problem with the code I received.
Situation: I import a 3 column list into a worksheet in Excel. On several
other sheets, I run a VLOOKUP code in VBA to autoenter data. When certain
criteria is entered in cell A1, VLOOKUP enters the corresponding results
into the same row into B1 and C1. I'm using the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As Variant
If Target.Address = "$A$1" Then
Set rng = Worksheets("Sheet2").Range("A1:C1800")
res = Application.VLookup(Target, rng, 2, False)
If IsError(res) Then
Range("B1:C1").Value = "Manual Entry Required"
Else
Range("B1").Value = res
Range("C1").Value = Application.VLookup(Target, _
rng, 3, False)
End If
End If
End Sub
This code only targets cell A1 on the current worksheet. I need it to
target about 100 rows of column A so if the data entered in cell A55 is
different than the data entered in cell A1, it returns the proper info for
data entered in A55.
I've tried using several Target.Range codes, to no avail.
Any help would greatly be appreciated.
Hi!
Does anyone here know how to go about filtering criteria based on three different columns, using xlFilterCopy from one worksheet to another?
I am using the code below now for 1 filter criteria, works great, but I need to filter 2 other additional columns... Does any one know how to mod this code to make this happen??
Your help will be appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 2 Then
Sheets("Parts Entry").Range("Criteria").Calculate
Worksheets("Parts Entry").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Parts Entry").Range("Criteria"), _
CopyToRange:=Range("A6:K6"), Unique:=False
Sheets("Part Search").Range("B3").Calculate
End If
End Sub
Thank you in advance!
Does anyone here know how to go about filtering criteria based on three different columns, using xlFilterCopy from one worksheet to another?
I am using the code below now for 1 filter criteria, works great, but I need to filter 2 other additional columns... Does any one know how to mod this code to make this happen??
Your help will be appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 2 Then
Sheets("Parts Entry").Range("Criteria").Calculate
Worksheets("Parts Entry").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Parts Entry").Range("Criteria"), _
CopyToRange:=Range("A6:K6"), Unique:=False
Sheets("Part Search").Range("B3").Calculate
End If
End Sub
Thank you in advance!
Excel Forum Experts,
Is there a simple VBA code that will move values from a selected range to another area of the same worksheet based upon criteria (selected from a pull down menu)? I have a value called "Completed" in Column A. When I filter all the rows in Column A that say "Completed", I want to move the values in that row from Columns B:E to Columns F:I.
I have looked at other discussions on this board but have not found the right code. Can you assist?
Thanks in advance for your help, greatly appreciated!
Hi...
I am a bit confused as how to even begin. I believe a match or index function will work, but i'm just not sure how to make it work.
First, I have a spreadsheet (master data) that pulls from an access database. The data goes back to 2006, and is updated whenever i hit refresh. This data is constantly being updated by over 300 employees. the reason i have this linked to excel, is that it is currently only about 40000 lines (so very manageable).
Second, what i am trying to do is compile various totals from this data set based on several sets of criteria ( i am currently using various sets of pivot tables, but i am now looking for specific criteria to send out in a "tally" fashion). The criteria will be based on Account Director, Target Date, Original Target Date, and Status.
As an example (that i can base other formulas off of), i would need to base the Account Director (in sheet 2, or my final list) to the "master Data" (found in sheet 1, where there is an account director column) set....then find a target date between 3/1/2011 and 3/31/2011...then with a status of "AD Hold"....each set of criteria is in their own column....
I am happy to provide more information.
Thank you in advance for any assistance you can provide!!!!
I am a bit confused as how to even begin. I believe a match or index function will work, but i'm just not sure how to make it work.
First, I have a spreadsheet (master data) that pulls from an access database. The data goes back to 2006, and is updated whenever i hit refresh. This data is constantly being updated by over 300 employees. the reason i have this linked to excel, is that it is currently only about 40000 lines (so very manageable).
Second, what i am trying to do is compile various totals from this data set based on several sets of criteria ( i am currently using various sets of pivot tables, but i am now looking for specific criteria to send out in a "tally" fashion). The criteria will be based on Account Director, Target Date, Original Target Date, and Status.
As an example (that i can base other formulas off of), i would need to base the Account Director (in sheet 2, or my final list) to the "master Data" (found in sheet 1, where there is an account director column) set....then find a target date between 3/1/2011 and 3/31/2011...then with a status of "AD Hold"....each set of criteria is in their own column....
I am happy to provide more information.
Thank you in advance for any assistance you can provide!!!!
Hi,
Am new to this so would greatly appreciate any help (and soon, please!)
Basically I need to constantly pull data from a database which will be exported to .xls in the Cell Range A1:F13 of the attached. (This will typically be much longer in detail).
I need to process the raw data to get summed, monthly data by the various categories as seen in the "Desired End Result" table.
The problem is, out of the exported data, there are groups (renamed to fruits and vegetables and highlighted) for which I need to sum the data (e.g. Apple+Banana+Cherry, summed to Apple, by month) BUT the search criteria is within the same column
I am using Excel 2003 and so do not have Sumifs (wouldn't know how to use it anyway). I have been trying to combine =Sumif and =Or but have come to realise Sumif can't work for multiple conditions.
Please help!!
Am new to this so would greatly appreciate any help (and soon, please!)
Basically I need to constantly pull data from a database which will be exported to .xls in the Cell Range A1:F13 of the attached. (This will typically be much longer in detail).
I need to process the raw data to get summed, monthly data by the various categories as seen in the "Desired End Result" table.
The problem is, out of the exported data, there are groups (renamed to fruits and vegetables and highlighted) for which I need to sum the data (e.g. Apple+Banana+Cherry, summed to Apple, by month) BUT the search criteria is within the same column
I am using Excel 2003 and so do not have Sumifs (wouldn't know how to use it anyway). I have been trying to combine =Sumif and =Or but have come to realise Sumif can't work for multiple conditions.
Please help!!
This:
=SUMIF($C$2:$C$1631,"<112",$D$2:$D$1631)
returns the number i expect it to return, the sum of all the values in the range D2:D1631 that correspond to the values in C2:C1631 that are below 112.
however, when i type 112 into cell L2, and try this:
=SUMIF($C$2:$C$1631,"<L2",$D$2:$D$1631)
i get 0 every time.
same with:
=SUMIF($C$2:$C$1631,"<(L2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<($L2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<($L$2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<(L$2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<"L2"",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<INDIRECT(L2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<OFFSET(L2,0,0)",$D$2:$D$1631)
I need to be able to change one cell and have many cells that have functions similar to that SUMIF function respond correctly. i cant just go in and change the "112" to "101" or whatever on every cell that has a SUMIF function.
Any thoughts?
Thanks,
=SUMIF($C$2:$C$1631,"<112",$D$2:$D$1631)
returns the number i expect it to return, the sum of all the values in the range D2:D1631 that correspond to the values in C2:C1631 that are below 112.
however, when i type 112 into cell L2, and try this:
=SUMIF($C$2:$C$1631,"<L2",$D$2:$D$1631)
i get 0 every time.
same with:
=SUMIF($C$2:$C$1631,"<(L2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<($L2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<($L$2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<(L$2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<"L2"",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<INDIRECT(L2)",$D$2:$D$1631)
=SUMIF($C$2:$C$1631,"<OFFSET(L2,0,0)",$D$2:$D$1631)
I need to be able to change one cell and have many cells that have functions similar to that SUMIF function respond correctly. i cant just go in and change the "112" to "101" or whatever on every cell that has a SUMIF function.
Any thoughts?
Thanks,
I'm trying to use a SUMPRODUCT function as a SUMIF with multiple criteria, but I keep getting a circular reference error.
Here's the problem: I'm trying to have one of the values in the range that I'm summing up be dependant on a calculation of the other values in that range. For example, let's say this is my data table:
A 1
A 2
B 3
B 4
C 5
C 6
D =SUMIF($A$4:$A$11,"B",$B$4:$B$11)
E =SUMPRODUCT(($A$4:$A$11="B")*($B$4:$B$11))
Now, interestingly, SUMIF actually returns a value of 7 as it is supposed to. However, the SUMPRODUCT returns a circular formula. This seems to be due to the fact that SUMPRODUCT evaluates every cell in the range in order to determine TRUE/FALSE, which is then assigned 1/0 values and multiplied across, which forces it to utilize the value in the last row even though it calculates out to zero, while SUMIF probably never even gets to the value in the last row because it excludes the row based on the criteria.
Here's my problem. I need to be able to insert mutliple criteria into the SUMIF, so I have to use a SUMPRODUCT structure. However, when I do this, I get a circular reference and the formula doesn't calculate. I've already considered the following solutions and unfortunately none are satisfactory for the model I'm trying to build:
1) Concatanated strings in hidden columns in order to enable the SUMIF function of a single criteria  this would be too cumbersome due to the dozens of variations of criteria I have to lookup.
2) Always leaving the row with the value of "E" at the bottom and excluded from the SUMPRODUCT range  this isn't possible due to the sorting and filtering that has to be enabled as part of the file.
3) Pivot Table  same issue as #1 plus the added burden of having to constantly refresh it. Not very user friendly.
I'm a beginner at VBA, but it seems like there should be a way to write up a SUMIF function with multiple criteria that operates with the same logic as the SUMIF function, not the SUMPRODUCT function. I'm open to other suggestions as well. Thanks for your help!
Edit: Forgot to mention, I use Excel 2003, (I know 2007 has the SUMIFS function)
Here's the problem: I'm trying to have one of the values in the range that I'm summing up be dependant on a calculation of the other values in that range. For example, let's say this is my data table:
A 1
A 2
B 3
B 4
C 5
C 6
D =SUMIF($A$4:$A$11,"B",$B$4:$B$11)
E =SUMPRODUCT(($A$4:$A$11="B")*($B$4:$B$11))
Now, interestingly, SUMIF actually returns a value of 7 as it is supposed to. However, the SUMPRODUCT returns a circular formula. This seems to be due to the fact that SUMPRODUCT evaluates every cell in the range in order to determine TRUE/FALSE, which is then assigned 1/0 values and multiplied across, which forces it to utilize the value in the last row even though it calculates out to zero, while SUMIF probably never even gets to the value in the last row because it excludes the row based on the criteria.
Here's my problem. I need to be able to insert mutliple criteria into the SUMIF, so I have to use a SUMPRODUCT structure. However, when I do this, I get a circular reference and the formula doesn't calculate. I've already considered the following solutions and unfortunately none are satisfactory for the model I'm trying to build:
1) Concatanated strings in hidden columns in order to enable the SUMIF function of a single criteria  this would be too cumbersome due to the dozens of variations of criteria I have to lookup.
2) Always leaving the row with the value of "E" at the bottom and excluded from the SUMPRODUCT range  this isn't possible due to the sorting and filtering that has to be enabled as part of the file.
3) Pivot Table  same issue as #1 plus the added burden of having to constantly refresh it. Not very user friendly.
I'm a beginner at VBA, but it seems like there should be a way to write up a SUMIF function with multiple criteria that operates with the same logic as the SUMIF function, not the SUMPRODUCT function. I'm open to other suggestions as well. Thanks for your help!
Edit: Forgot to mention, I use Excel 2003, (I know 2007 has the SUMIFS function)
So I've trying to create a formula that will mimic a sumif function but using multiple criteria. One of the criteria is to look for a certain character string that could be inside of a cell that contains other characters as well. I know if I do a sumif function I can use a formula like this:
=SUMIF(Sheet1!$B:$B,"*" & "Test" & "*",Sheet1!$F:$F)
That would look in row B for any cells that had the string Test anywhere inside the cell and then sum up column F. My problem is that I need a formula that will look for a certain string as well as make sure it matches another value. I've tried using this formula:
=SUMPRODUCT((Sheet1!B1:B100="*" & "Test" & "*"),(Sheet1!C1:C100=20),Sheet1!F1:F100)
That is looking in column B for a string of Test and then also making sure that column C = 20. Fopr any matches it sums up column F. The formula doesn't work though because it doesn't allow the function I have setup to look for Test. Can anyone help me with a solution. Thanks
=SUMIF(Sheet1!$B:$B,"*" & "Test" & "*",Sheet1!$F:$F)
That would look in row B for any cells that had the string Test anywhere inside the cell and then sum up column F. My problem is that I need a formula that will look for a certain string as well as make sure it matches another value. I've tried using this formula:
=SUMPRODUCT((Sheet1!B1:B100="*" & "Test" & "*"),(Sheet1!C1:C100=20),Sheet1!F1:F100)
That is looking in column B for a string of Test and then also making sure that column C = 20. Fopr any matches it sums up column F. The formula doesn't work though because it doesn't allow the function I have setup to look for Test. Can anyone help me with a solution. Thanks
I looked through the forum, and couldn't find anything exactly like my problem.
I cannot use VBA.
My problem is this:
I have a spreadsheet with multiple columns: Column A, B, C, D, etc. I need to know the sum of UNIQUE values in column A where certain conditions are met in Columns C, D, E, etc.
For one row, I expect the forumula would look something like the following:
If (Column B == "crit1"){
if (Column C == "crit2"){
if (Column D != "crit3"){
count_unique_values(Column A);
}
}
}
Of course, nothing like this will work since my sheet has multiple rows with a mix of values.
Note that only UNIQUE values in column A shall be counted.
How could I expand the equation to include additional criteria in other columns?
Thanks in advance! Please let me know if there are any questions.
I cannot use VBA.
My problem is this:
I have a spreadsheet with multiple columns: Column A, B, C, D, etc. I need to know the sum of UNIQUE values in column A where certain conditions are met in Columns C, D, E, etc.
For one row, I expect the forumula would look something like the following:
If (Column B == "crit1"){
if (Column C == "crit2"){
if (Column D != "crit3"){
count_unique_values(Column A);
}
}
}
Of course, nothing like this will work since my sheet has multiple rows with a mix of values.
Note that only UNIQUE values in column A shall be counted.
How could I expand the equation to include additional criteria in other columns?
Thanks in advance! Please let me know if there are any questions.
Hi Guys,
I am trying to write a macro that will filter the data out of one workbook and paste it in another based on the criteria provided for in column A and C. The criteria for A is fixed say
"1" but the criteria for column C is going to be an array for 200 different items.
Also i want the macro to look through all the worksheets of a workbook as i have three worksheets with 65536 line items. and copy data to new workbook.
I have attached a dummy data sheet for reference.
So, my criteria for coulm A is "1" and array of criteria for colimn C is (" AA, BB, , DD, FF, GG, HH, JJ").
I want to macro to set Column A on "1" and then cycle through the criteria on Column C copy and pasting the data in to new work book, it will reset the filters each iteration. If there is error like it cant find any criteria in column C show message and continue to next criteria .
Any help will be highly apprecited.
Thanks.
Roop
I am trying to write a macro that will filter the data out of one workbook and paste it in another based on the criteria provided for in column A and C. The criteria for A is fixed say
"1" but the criteria for column C is going to be an array for 200 different items.
Also i want the macro to look through all the worksheets of a workbook as i have three worksheets with 65536 line items. and copy data to new workbook.
I have attached a dummy data sheet for reference.
So, my criteria for coulm A is "1" and array of criteria for colimn C is (" AA, BB, , DD, FF, GG, HH, JJ").
I want to macro to set Column A on "1" and then cycle through the criteria on Column C copy and pasting the data in to new work book, it will reset the filters each iteration. If there is error like it cant find any criteria in column C show message and continue to next criteria .
Any help will be highly apprecited.
Thanks.
Roop
I would like to find a way to write a formula that will return the value in the cell based on multiple criteria. I have two criteria columns and one header row that is a criteria. My problem is that the input is often between the set of numbers in the criteria. I've attached a very simplified example of how the spreadsheet is established. My criteria is in rows 1820. I can't seem to hit the right combination of formulas to pull the correct result from the full range (D5:M13) that matches that criteria. Any help would be appreciated.
I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
Hi,
I have a worksheet of data that I'm trying to extract particular values from.
Column J contains the name of the quarter eg "Q1 20056" I've used COUNTIF to calculate how many records meet that criteria.
In another cell, I'd like to narrow my search with additional criteria.
Column L contains numerical values. I would like to be able to calculate how many of the "Q1 20056" records meet certain criteria in the L column. For example how many of them contain a value of less than "32".
I can work out each value seperately but would like to be able to create a function that allows me to set both criteria to get the result neatly in one cell.
Can anyone make any suggestions?
Thanks
I have a worksheet of data that I'm trying to extract particular values from.
Column J contains the name of the quarter eg "Q1 20056" I've used COUNTIF to calculate how many records meet that criteria.
In another cell, I'd like to narrow my search with additional criteria.
Column L contains numerical values. I would like to be able to calculate how many of the "Q1 20056" records meet certain criteria in the L column. For example how many of them contain a value of less than "32".
I can work out each value seperately but would like to be able to create a function that allows me to set both criteria to get the result neatly in one cell.
Can anyone make any suggestions?
Thanks