Email:      Pass:    Pass?


Advertisements


Free Excel Forum

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

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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


Similar Excel Video Tutorials

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
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
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
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
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
- This Excel macro filters data in Excel to display results that contain 1 of 2 possible values. This macro uses the xlor

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 sub-header 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


Hello,

So I learned on this fantastic forum how to use SUMIF. Next, I need to know how to introduce multiple criteria. An extract of the data columns is shown below.
Column A will have several different ID values, so SUMIF has to find a specific ID first, e.g. 58. Once that is identified, It has to find all B = 150 and Sum the values in D.
Column B will contain one or more of 150, 225 or 300.

BTW, I have just discovered SUMIFS function, and it should do the trick, but I can't get it to work. Here is my version of it:

=SUMIFS(sewers_sorted!D2:D10770,sewers_sorted!A2:A10770, A22, sewers_sorted!B2:B10770,"150")
It results in a #NAME! error. I can't see a problem in it.

A B C D
58 150 UPVC 16.30
58 150 UPVC 79.92
58 150 UPVC 51.80
58 150 UPVC 60.09
58 150 UPVC 41.91
58 150 UPVC 45.61
58 150 UPVC 58.74
58 150 UPVC 33.94
58 150 UPVC 45.77
58 225 UPVC 6.02
58 150 NA 82.02
58 150 NA 81.85
58 300 NA 33.76
58 300 NA 89.43
58 150 NA 15.74
58 225 AC 8.73


Thanks for your help.


i need to do a sumif on a range in another worksheet specified in a cell in the current worksheet. the sumif criteria is also in the current sheet.I am doing this now with a formula and it works well. The formula is below SUMIF((INDIRECT("'"&$D4&"'!"&"$s$3:$s$"&(MATCH("---------------",(INDIRECT("'"&$D4&"'!"&"a:a")))+1))),aaaaa!BQ$2,(INDIRECT("'"&$D4&"'!"&"$l$3:$l$"&(MATCH("---------------",(INDIRECT("'"&$D4&"'!"&"a:a")))+1)))) where the sheet where the sumif is to be carried out is stored in D4 (example:"worker01")and the criteria for sumif is in aaaaa!BQ2 (example:"aug2010").the formula then searches range S:S in the target sheet and sums the matching values in range L:L and returns the value.i was looking at a funtion to simplify this.(example:=vmtotalss(d4,bq2,l) where D4 contains the target sheet name,BQ2 contains the text used as sumif criteria and l would be the range that needs to be totalled) i am using the match to determine the size of the range in the target sheet as each sheet could have variable number of rows but thelast row definitely has the text which i am matching for(viz:"-----------------") Also i am new to VBA and wish to learn.could you suggest a good book for learing it. VINAY MAGADI

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.


Hi,

I set up a SUMIF forumula shown below;

Please note that the formula is set up in "Data Table 2":

"=SUMIF('Data Table 1'!E5:E100,A3,'Data Table 1'!G5:G100)",

where within column E - cell 5 to 100, the criteria in cell A3 (in Data Table 2) is searched for and then adds all the numberical values with in column G - cell 5 - 100 (in Data Table 1) that correspond to the criteria in cell A3.

This formula works well for what I needed, but I need somthing a little different for my next project.

I need to search for a criteria in a column A, once I have found all the cells (there may be more than one cell that meets the criteria) that meet the criteria, I need to find in column B all the cells that have the word "Incomplete" associated within the row that meets the criteria. So...is there a way I can combine SUMIF and VLOOKUP formulas to do this.

Thanks in advance for any help

Calli


Hi All,

I'm trying to automate advanced filter but can't get past having just 1 critreria.

I have attached an example spreadsheet where the download dump is on "Data" sheet and the result of the advanced filter is on the "Result" page.
I would like, if possible, for the result to come up every time the criteria is put in (they are all data validation lists).

so for example, if i select account code, all those codes come up then select period and the list dwindles down and again for cost centre.

Is it possible to do this without using macros. I have found the following code butnot sure what to do with it (sorry).

Many thanks for any help.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 3 Then
'calculate criteria cell in case calculation mode is manual
  Worksheets("ProductsList").Range("G2").Calculate
  Worksheets("ProductsList").Range("Database") _
    .AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=Sheets("ProductsList").Range("G1:G2"), _
      CopyToRange:=Range("A6:D6"), Unique:=False
End If
End Sub





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


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.


so for example - column "D" has 582 rows and each cell has 15 different values (1 to 9 and A to F) so i need to select all the instances of say the "C" of the 582 - now the tricky part i need to then have it tell me for all of the "C" values what is the "countif" total in the "M" column for only the "C" values. there are four possible values to count in the "M" column. if needed i can upload a sample of the sheet. also if it is something easy to duplicate for needing it multiple instances (15 of them like 5 times each) selecting the columns and then replacing the criteria would be best. sorry for the lack of terminology. anyone that can help i would appreciate it more than words can even begin to explain. Thank you. Abel.

PS the entire title did not fit :
sort and report multiple criteria from multiple columns and report back a count to one cell the # of entries


Hi,

I've a lookup table with 5 columns.
column A is the criteria for searching/lookup and column B-E are
numerical Data.

e.g.
Col A Col B Col C Col D Col E
Name Result1 Result2 Result3 Result4
Amy 10 20 30 40
Ben 11 22 33 44

If I'd like to know the total of mixed results (such as Result1+2,
Result 1+2+3),it's not possible for sumif to do a sum range in array
e.g.
=sumif(A:A,"Amy",B:C)
=sumif(A:A,"Amy",B:D)
don't work.

However, writing nested formula like:
=sum(sumif(A:A,"Amy",B:B),sumif(A:A,"Amy",C:C),sumif(A:A,"Amy",D:D),sumif(A:A,"Amy",E:E))
will drive people crazy.

Is there anyway to perforum this addition?
Thank you.




I need help counting the amount of data points that meet multiple criteria in an array. For example:

Criteria 1 ______ Criteraia 2 ______ Data 1 ______ Data 2 ______ Data 99 ______ Check1 ______ Check2
__ 10 ___________ 50 ___________ 11 ___________ 75 _________ "TEXT"
__ 20 ___________ 25 ___________ 30 ________ "TEXT" ___________ 1

I need the Check1 column to count all of the Data Columns (Data 1 through Data 99) that exceed criteria 1 but do not exceed criteria 2. Also, it should omit all non number values, such as Data 99 which is labelled as "TEXT". Likewise, I need Check2 to count all Data Columns that exceed Criteria 2 and omit all non number values. Countif function seems to be limited to only one criteria and can't seem to deal with the ISNUMBER() function as a criteria. Any ideas on how to complete this?


Hello everyone!

I need a formula that returns summed data with two criteria:

1. the value of range is smaller than value of other row values (dates involved)
2. sum only numerical values, i.e. in the sum_range there are #N/D values returned by VLOOKUP function which I would like to ignore

When I use this: =SUMIF($O$1:$BH$1;"<"&I2;O2:BH2) I get errors back where there are errors in the sum_ranges . How can I sum only numerical values in O2:BH2 range and ignore errors?

Any ideas?

Thanks in advance


Hey, I'm trying to fix a code I wrote at work for makring things done.
I want an optimized version as well as a few new functions.
new functions wanted and questions:
- Two colors that varry each day to make it easier to read, these cover a custom range of columns on row in use. (no color and orange, assume the function would compare dates found something like that on forum though it doesn't work because using Now() includes time)
- One color for third x value. (10th column)
- Calculate per/hour, per/day and per/month stats by counting entered values.
- Any way to connect and fetch values from Telnet client?
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Text = "" Then
    If Target.Column = 4 Or Target.Column = 7 Or Target.Column = 10 Then
    Target.Offset(0, -1) = ""
    Target.Offset(0, -1) = ""
    Target.Offset(0, -2) = ""
    Target.Offset(0, -2) = ""
    End If
   End If
  If Not Target.Text = "x" Then Exit Sub
  If Target.Column = 1 Then Exit Sub
  If Target.Column = 2 Then Exit Sub
  If Target.Column = 3 Then Exit Sub
  If Target.Column = 5 Then Exit Sub
  If Target.Column = 6 Then Exit Sub
  If Target.Column = 8 Then Exit Sub
  If Target.Column = 9 Then Exit Sub
  If Target.Row = 1 Then Exit Sub
   If IsEmpty(Target(1)) Then Exit Sub
   If IsEmpty(Target.Offset(0, -1)) Then
    Target.Offset(0, -1) = Date
    Target.Offset(0, -1).NumberFormat = "dd-mm-yyyy"
    Target.Offset(0, -2) = Time
    Target.Offset(0, -2).NumberFormat = "hh:mm:ss"
   End If
End Sub


Thanks in advance, now I have to work.


Hi,

I have a spreadsheet that is set up to collect data based on a source tab.

I am also introducing a new criteria into the source tab (Data Table 1) which will affect all the other tabs, the new criteria that I am introducing is "Process/General".

And based on the new criteria, all other formulas will change as well because the formulas (with 2 or 3 conditions) will now have this criteria included in them.

I just don't know how to incorporate this new criteria into the formulas.

For Example:

I have the following formula in the cell B16 of tab "B"

"=COUNTIF('Data Table 1'!$D$7:$D$271,A16)"

but now I have to introduce the criteria to count only if in tab "A" within column B (B7:B27) that meets the condition of "G"



Also,

in tab "B", I have the formula "=SUMIF(A!$D$7:$D$329,A16,A!$G$7:$G$329) and again I need to do what I mentioned above.

I tried it our myself but could not figure it out....can anyone please help, I've attached the document that I am referring to in the above.


Hello everyone,

I wonder if this has been asked before but I am sure that it is a question many people have come up with in the past.

I have a table with data that has two main columns. I will call them "criteria" and "data". What I need to do is find and list all those values in "data" for which "criteria" meets a certain condition, in this case, those for which "criteria" = "a".
Is there a way to do this?

Thanks!!


Code:

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,



I am having issues creating a formula that will give me a set value based on multiple criteria.
Example:
If Col A="yes"
AND if Col B="No"
AND if Col C= "2"
AND if Col D= "B"
AND if Col E= "X"
Then "Col F"
if not "0"

I am putting together a multiple sheet workbook that is used as a template to submit to the Medicare. I am currently having to manually look up and enter all information for each discipline. What I want is for a cell to look on another worksheet search data in 5 or 6 columns and if they all match the specific criteria asked for it returns the value listed.

The data worksheet contains about 1500 lines and 30 columns total...I only need to use a specific few columns, but every line.

We have tried mutiple/nested if statements and and statements (although not sure I am doing them right) and always get an error about too many criteria.

Any help would be appreciated, thanks!


I have a spreadsheet listing names,dates and values. I am attempting to list all the names and values which match set names and dates.

I've tried to split this into two problems, both of which I couldn't solve; how do I increment the cell I'm displaying the results in once I have found the first value, so I don't write over my initial results and how do I search for the next set of results matching the same criteria.

I have attached the table of results I have been using for testing. Below this is the results which I would expect to find had everything gone well, when Name= "WMIN" and Date= "07/05/2007".

I have attempted to use "vlookup, if, sumif, and" formulae but nonw of them return the results I require.

Thanks for any suggestions.


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 an expenses sheet set up as follows:

A/status B/date C/expense type D/amount

1 allowed 05/07/06 car £20.00
2 notallowed 05/07/06 car £450.00
3 notallowed 05/07/06 car £15.00
4 notallowed 05/07/06 car £26.00
5 allowed 05/07/06 post £20.00
6 allowed 05/07/06 post £20.00
7
8 total car allowed
£--.--
9 total post allowed
£--.--
10
11 total car notallowed
£--.--
12 total post notallowed £--.--


what formula can I use to say total all instances of "car" & "allowed"...
or of "car" & "not allowed"

I have tried =SUMIF, but it will only recognise the first column in the
range..
eg in D8 I wrote:

=SUMIF(A1:C6, "allowed""car", D1:D6)

but it will not recognise multiple criteria ie "allowed" & "car".

how can i total the values based on multiple criteria in different columns?

thanks in advance

nicky



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 hard-coded 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"),'By-Month 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 By-Month 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.

Hi
I'm fairly new to VB and can do simple routines but am struggling with this problem.
I would like a macro that will rationalise my data by summating a number of entries that match a certain criteria. I have data in 9 columns starting at B2 in over 5000 rows and I would like to reduce the row numbers by summing similar entries on a single row based on the following criteria:
Where the variables in Column B are equal to "D100", "D101" and "D102" and the values in Column I and J are the same, sum the column F values on a single row. Basically I and J are the month and the year so for the D100's I want to sum the values that appear in the same month and year. For non D100's the rows will remain unchanged.
It's difficult to explain so I've attached a small sheet showing the Before and After to make things clearer.
Any help would be very much appreciated.
Regards,
Danny


Hello, I am trying to find a formula that will conditionally sum a column of data (i.e. a sumif) based on whether the data is associated with (across from) a cell that matches a list of criteria from another tab. I've used a sumproduct formula that i've found elsewhere on another post.

The current formula reads:

=SUMPRODUCT(--(ISNUMBER(MATCH('Lookup Sheet'!$B1:$B1000,Criteria!A2:A14,0))),'Lookup Sheet'!$D1:$D1000)

My criteria are in a list A2:A14, the column to search in is in column B and with the associated data i'm looking to sum is in column D on 'Lookup Sheet'.

The catch is i'd only like the formula to sum if it finds a match to one of the criteria if its the 1st occurance of that criteria. So right now the formula i'm using returns double the answer i'm looking for certain criteria matches, e.g. "landscaping" because it occurs twice in the list i'm searching through.

I've attached an example of what i'm trying to do with the current formula in place on the "criteria" tab. The data i'm trying to conditionally sum based on potential matches to the multiple criteria is on the tab called look-up sheet.

Many thanks,
Eric


Hi,

I need to count the number of cells which is matching with my criteria form two columns. But my requirement is, if it is matching with the criteria of 1st cell of 1st column, then only it should match the criteria for 1st cell of 2nd column.

For exp:

Tom True
Can False
Can False
Tom True
Tom False
Tom False

So I need to check how many times, "Tom" is having "True" values. In this case, the value is "2".

I found one formula but it is not working:

=SUM(IF((A1:A6="Tom")-(B1:B6="True"),1,0))

It is giving result as 4, instead of 2.

Can anyone give me any suggestion to get the result.

Thanks & Regards,
Pramod


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