Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

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.


View Answers     

Similar Excel Tutorials

AutoFilter with an Excel Macro
This Excel tip shows you how you can use an Excel Macro in order to run a Filter on data within a spreadsheet. Filt ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...
Quickly Replace A Lot of Data in Excel
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...

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

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.

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

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:


Any suggestions or advice would be helpful.


I have a column of 600+ values (English football positions) and would like to find and replace them based on some criteria. An example spreadsheet is attached. Example.xlsx

The column is something like:


The criteria I have is that any value with "M" (i.e. M(C), M(CL), DM(C)) should be replaced by "MF". Any value with "D" (i.e. D(R), D(C)) should be replaced with "DF".
[Note here, that DM(C) contains both "D" and "M" but "M" condition to take priority]
FW remains as FW.
GK remains as GK.
For cells with two values, both values should be replaced based on the criteria and remain separated by a comma.
E.g. D(R), DM(C) should be replaced by DF,MF
But, if the two values overlap, they should be replaced by just one.
E.g. DM(C),M(L) should be replaced by MF

This is a bit out of my league. Is there any easy way to do this?

Thank you

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:


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!


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. there a way I can combine SUMIF and VLOOKUP formulas to do this.

Thanks in advance for any help


Been banging my head on the keyboard for last 2 hours trying to figure out how I used to sum multiple criteria within a single range when using {"text","text"}, but not working.
This formula only gives me the 10-1result, and I need to add for columns up each for the day, with 16 different criteria??
Also, I don't really want a 15 line formula for only adding 4 criteria up over 4 columns. Is there a way to sum all 4 periods. P1,P2,P3,P4, only adding the 10-1, 10-2,10-3 criteria? 10-1,10-2 etc cells are dropdown lists.

Sorry, my small file wouldn't upload?

name P-1 Hrs P-2 Hrs P-3 Hrs P-4 Hrs
a 10-1 8.1 13-2 1.0 NA NA
b 10-1 8.1 NA NA NA
y 10-1 8.0 NA NA NA
d 10-3 8.0 NA NA NA
r 10-1 7.5 NA NA NA
f 10-1 8.0 NA NA NA
g 10-2 9.0 NA NA NA
gh 10-1 10.0 NA NA NA
i 10-1 11.0 NA NA NA
j 10-2 14.0 NA NA NA
k 10-1 13.0 NA NA NA

Hey Everyone.
I downloaded an Advanced Filter Macro from ( and it works great.

I was wondering if anyone can help me set more criteria.

On the Worksheet it has the code


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.

Hi guys,

What I'm trying to do is too complex for a regular Excel formula, so I need a VBA. Unfortunately, I know very little about VBA, so I'm really counting on this forum. I think this might be a challenging VBA, but hopefully fun too!

It's easiest to begin if you look at the attached workbook (My apologies that the formulas are hard to read, they refer to another workbook). As it is now, column CH has a formula that takes the sum of the top three and bottom three values in columns BV:CG. The criteria for "top" and "bottom" is based on the ranks in AX:BH--i.e. if a cell's corresponding "rank" is in the top three highest or lowest in all of the ranks of that row, then that cell is added to the sums in CH. *The formula also only runs if the sums of all corresponding ranks on that row add up to 45* Formula=(IF(SUM($AX4:$BH4)>=45,SUM(SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4))+SUM(SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)),FALSE))

As it is now, it's wrong.
First: Instead of using different ranks each day, I need to use the same criteria for the whole work week (5days). In other words, I need to target these parts of the above formula "SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and "SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and change them so that the large{1,2,3} and small {1,2,3} of $AX4:$BH4 hold for BV4:CF8, i.e. the whole week.
Second: The criteria on the last day of the proceeding loop is the criteria for the first day of the next loop. In other words, thought about in a real life scenario, one can only make a decision for that week based on the criteria of the day before.
Third: If the loop hits a "FALSE" statement (which would happen if the sum of the ranks is less than 45), I need excel to take the next available criteria, and apply that to the next 5 days.
Fourth: This (above statement) means that the loop needs to be linked to a calender. I can't just apply the same criteria to 5 cells, the next criteria to the next 5 cells, ect, because on the FALSE days, there would not be a sum, but instead the sum would pick back up at the next available criteria, and THEN apply that to the next 5 cells.

Even a partial code or input would be helpful. I'm really running on nothing.


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

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
don't work.

However, writing nested formula like:
will drive people crazy.

Is there anyway to perforum this addition?
Thank you.

In my spreadsheet the column "C" can contains all values of 0,1,2,........10.

What exactly i need is, if the column "C" contains any value other than 0, in need a msg box that "values greater the 0 found"

i tried using below code
if activesheet.range("C:C").autofilter:=3,criteria:=">0",then
msgbox"values greater the 0 found"

But i find this not working.Can someone help on the above

Hi All,

Currently having major excel issues. I've got a spreadsheet with multiple columns of data all feeding from different sources, and I need to do an average based on multiple criteria.

So I currently have to sort one column alphabetically so that I can do an average if with the range being the range of few values from the column which i have.

So they currently look like this:
=AVERAGEIF($T$72:$T$222,"Criteria B",$AG$72:$AG$222)
=AVERAGEIF($T$2:$T$71,"Criteria A",$AG$2:$AG$71)

where $T$72:$T$222 is Criteria C, and $T$2:$T$71 is Criteria D
Column AG is where the data I want to average is.

Ultimately I want to make it so that excel will do an average of all the data which has all the criteria required.

Thanks for any help!

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:


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.


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


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
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 non-zero matching the >0 criteria for rows 2 & 4.

IMAGE of my current usage (but WITHOUt COL A criteria) is attached.
Screen Shot 2013-09-03 at 8.34.54 PM.png



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.



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"


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.

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.

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



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 ?


Here's my code:

Please Login or Register  to view this content.



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


calling all experts

I need some assitance as this is way out of my league. I have 6 criteria that i can use sumproduct on , however the data is laid out in a rather unique manner.

criteria 1 = "completed" (column D)
criteria 2 = "created" (column E)
criteria 3 = "revenue" (row 16)
criteria 4 = "period or month" row 15
criteria 5 = "revenue" (column C)
criteria 6 = "city" (column B)

i have attached my workbook; there may be an easier way or so but i think sumproduct with offset will do the trick, however i have no idea where to start?

in cell C8 i have given what the end value should be and from what cell. i addition, all yellow cells are drop down list so they can change.

if someone can pls help, that would be truly appreciated. thank you so much!!! i have 800 rows of data in my master so a formula would be great, if someone can pls devise one.

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
8 total car allowed
9 total post allowed
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
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