Free Microsoft Excel 2013 Quick Reference

Using multiple criteria in SUMIF Function

Using multiple criteria in SUMIF Function

ExcelTip.com offers free Microsoft Excel Tips, Microsoft Excel Tutorials, and Excel Spreadsheet Help for Microsoft Excel 97 / 2000 / 2002


Post your answer or comment

comments powered by Disqus
Can I use multiple criteria in SUMIF funqtion?
For example, I want date and quantity sold to be criterias that filter out
the data and I do not want to use DSUM funqtion. I am using Excel 2003.

SUMIF Using Multiple Criteria in Microsoft Excel

The SUMIF function doesn’t work for multiple criteria in different fields. However, you can use an array formula (sometimes referred to as a CSE formula), which requires ...

I'm aware of the SUMIF function that sums up a range based on one criteria. But I'm wondering if there's a function that does the same thing but allows multiple criteria in a SINGLE column.

The SUMIFS function only applies if you have multiple criteria spread across multiple columns *not* in the same single column.

So, any ideas on this? Thanks in advance.

is it possible to use two criteria in with a sumif function?

what I want to do is sum a range of numbers if a location code matches and a currency code matches.

example WS

location code Currency Code inventory
1 $
3 $
1 Y
1 $
3 Y

I want to sum all the inventory for the amounts with location code+ 1 and currency = $.

I can get a sumif function to work using one criteria ie sumif location code matches but I am having trouble excluding the numbers I don't want.

thank you for any help

I am trying to retieve a value using multiple criteria, the vlookup function is not appropriate as I have repeat values in the same column. I am finding it diffcult to explain what I need in words so i will use an example

With reference to the attached table how can I return the Link if I know the 'Road No' and the 'SLK' ie. if I have H005 as the road and 560 as the SLK how can I get this to return Link 15

For extra clarity 'SLK' is the distance along the road and each Link runs from 'SLK Start' to 'SLK End'

I will appriciate any help I can get. Thanks

Does anybody know how I can lookup multiple criteria in the same way as VLOOKUP except I need the syntax to be LOOKUP(values,table array columns 2-4,column number).

My data is in the following format

Ref 1 Field 1 Field 2 Field 3 Output
Ref 2 Field 1 Field 2 Field 3 Output
Ref 3 Field 1 Field 2 Field 3 Output

where I want the output coumn to look across Field1..Field 3, and match all of them to the first 3 columns of an array, and return the value in the designated column number. Tried the database functions (DGET,DSUM etc) but they require the headers in the formula which only works for the first row.

I have successfully used the sumif condition, but am not able to figure out how to use multiple conditions in sumif.

For eg: there are 3 columns A,B & C, how do I perform this calculation:

Sum of value in C for all, where A=1 & B=3

Can anyone help me??

I am looking for a function to search through multiple ranges of cells
and return a number if it finds any instances of that number.

For instance:
I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of
them contain the number "1" and if they do, I want the result of B2 to
be "1".

The way I thought I could accomplish this was to go to cell B2 and do
the formula =if(countif((A1,C17,F13:G14),"1")>0,1,0)

This seems to work for a single range such as
=if(countif(F13:G14,"1")>0,1,0)

I just dont know the proper syntax for including multiple ranges in a
function.

If anyone knows how to include multiple ranges in a function (if its
possible), or knows a simpler way to do what I am trying to do, the
help would be greatly appreciated.

--
solinar
------------------------------------------------------------------------
solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159
View this thread: http://www.excelforum.com/showthread...hreadid=508216

I am looking for a function to search through multiple ranges of cells and return a number if it finds any instances of that number.

For instance:
I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of them contain the number "1" and if they do, I want the result of B2 to be "1".

The way I thought I could accomplish this was to go to cell B2 and do the formula =if(countif((A1,C17,F13:G14),"1")>0,1,0)

This seems to work for a single range such as =if(countif(F13:G14,"1")>0,1,0)

I just dont know the proper syntax for including multiple ranges in a function.

If anyone knows how to include multiple ranges in a function (if its possible), or knows a simpler way to do what I am trying to do, the help would be greatly appreciated.

Dear All,

Is there any way to use multiple conditions in CountIF Function or any other way / alternative.

Multiple Databases and Criteria in Database Functions

You can specify more than one database and/or criteria for use with the database functions [DSUM(), DCOUNT(), and so on] in Microsoft Excel. The database functions will accept any ...

How do I sum (like SUMIF) but predicated on multiple criteria, in Excel. I
belive there is a single function that will resolve this question. PS I do
not want to filter the document by column than sum.

I have the following data:

Account External ref 2 Value
5950.799.MR05 976 158.64
5950.799.MR10 976 84.96
5950.799.MR05 978 108.12
5950.799.MR15 506 158.64
5950.799.MR05 506 65.44
5950.799.MR05 976 83.28
5950.799.MR05 506 103.12
5950.799.MR10 976 158.64
5950.799.MR10 976 158.64

I would like to use a SumIf function using two criteria. If the
account number in Column A = 5950.799.MR05 and the Ext Ref 2 (Column B)
= 506 then sum the value in Column C

I tried using the And statement in conjuction with the SumIf but it
didn't work.

Any help would be greatly appreciated!

Thanks!

I would like to use Excel's SUMIF function to get the sum of a field in a
database using TWO criteria. That is for example, get the sum of field A if
the data in field B is one value and the data in field C is another so that
I have the sum of A's when B and C meet their criteria.

I do it now with a pivot table but would like to eliminate the table.

Is this possible?

Thank you very much.

hi all,

is there any way for a sumif formula to have multiple criterias? for my case, after the formula checks for a condition, it has to check for another condition before summing up the figures.

this is my current formula:

E2 contains a certain id and the formula will total up the figures in column F for that particular id. i want to add another
condition that the formula has to check for another condition before summing up. it has to check the description in column C
for these words 'OR'. only after that it can total up for the id AND with the words 'OR'.

i want to be able to use wildcards for 'OR' since the descriptions might contain 'AW OR', 'OR (ST)' etc.

how do i add the other condition into the formula? pls help.

Excel: SumIf with Multiple Criteria. Excel Formulas & Functions

Excel: Sum If with Multiple Criteria. How to Sum cells that meet multiple criteria. Excel Formulas & Functions

I could use an "array formula" suggestion on my problem..........

I've created a database in Excel, and I am trying to use the array formula
below to count for me, but it is only working if I use a single search
criteria in Row AN (e.g. AC Initiated or Referred to Tx) but not both.

What I'd like to do (as the formula is attempting to execute) is count all
client's who were seen during the 2007 calendar year, and identify those 2007
client's who were "referred to tx" and those who began tx (e.g. AC
Initiated). I didn't know if an Array formula can search for multiple
criteria in the same row twice (once for all 2007 clients who were "Referred
to Tx" and then again for all who initiated tx (AC Initiated). I've tried
several variations of the formula below with no success. Any suggestions?
Thank you in advance, Dan

The Formula I was using that isn't working is:

=SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="AC
Initiated")*(AN4:AN3500="Referred to Tx"))

I have been counting the occurance of model numbers using multiple
criteria over two or more columns. I've been using SUMIF with multiple
criteria in an array, but my problem is that, because I have 40,000 rows
of data, it takes an age to do the calculation.

Is there an easier/quicker way?

Example:
{=SUM(IF(($K$6:$K$40002=B127)*(LEFT($N$6:$N$40002, 2)="PR")*($L$6:$L$40002="V"),1,0))}

Thanks

--
andrew.curley
------------------------------------------------------------------------
andrew.curley's Profile: http://www.excelforum.com/member.php...o&userid=35326
View this thread: http://www.excelforum.com/showthread...hreadid=550996

Is there a way to find the first row in a data set that meets multiple
criteria?
Something that works like =MATCH(Criteria,Array,0) except for multiple
criteria in multiple columns.
I would also like to know if there is a way to find the LAST row that meets
these criteria.

I am using SUMPRODUCT to count and sum in a large database using predefined
ranges.
I am trying to to speed up calculation by limiting the defined ranges to
include only the applicable rows, instead of the entire database.
I'm using 5 conditions that the user defines with drop down boxes.
The conditions for the date range include >= and <= in the sumproduct
functions.

I can do this by using an extra column:
=IF(AND("condition1 is met","condition2 is met",etc),="cell above"+1,="cell
above")
copy down, then
=MATCH(1,"extra column",0) gives the first row
=MATCH(MAX("extra column"),"extra column",0) gives the last row

However, even though 60,000 of these IF statements (I have 10,000 rows and 6
user defined groups, time periods, etc in the report) only take 3 seconds to
calculate on their own (using calcualte worksheet), the entire report takes
about 20% longer to recalculate.
We can live with it for now, but we keep adding data.

If the answer is no, and this won't work to speed up calculation, that's
okay.
Please tell if that is because there is no better way to match on multiple
criteria, or because it will slow the calculation anyway..

Thanks for any help you can offer.
Bob

Please see the bottom of this sheet for what i am looking for. I am trying to count the number of platinum units, per unit type...(1x1, 2x1, 2x2)

I am doing this for multiple properties and there seems to be an issue when counting 2x2s. For some reason it has come up one short for this unit type on multiple occassions.

in instances where there is only one criteria...i have been able to get around this problem by using:

SUMPRODUCT(--(TRIM(my range)="2x2")) and it counts correctly. But i am at a loss when i need to count using multiple criteria. the "countifs" function worked for everything but the 2x2s and it worked for that but was short by 1 unit?

so 2 questions:
1) why is it continuously not working for 2x2 units?
2) how can i get it to count correctly in situations where i have more than one criteria?

Thanks.

I am using Excel 2003. I created an application using GUI that asks for input and then goes to a worksheet. All the information is kept on one worksheet. I have created several reports based on the criteria in certain columns. That copy and paste only pertinent information onto a new sheet. These reports work however I can not make the reports work using multiple criteria such as if column a = "Accepted" and column b = "Pass". I believe I am having issues with my loops. Here is an example of a report that works currently but with only one criteria.


	VB:
	
 
Private Sub CommandButton1_Click() 
    Application.ScreenUpdating = False 
    Sheet10.Visible = xlSheetVisible 
     
     
    Dim temp As String 
     
    Sheet10.Activate 
    Dim ocell As Range 
    Dim bnone As Boolean 
    bnone = True 
    Dim current_row As Integer 
     
     
     
    For Each ocell In ActiveSheet.Columns(1).Cells 
        If IsEmpty(ocell) Then 
            ocell.Select 
             'MsgBox ("found at " & oCell.Row)
            bnone = False 
            Exit For 
        End If 
    Next 
    current_row = ocell.Row 
     
     
    Dim blah As String 
    Dim blah1 As String 
     
    Sheet3.Activate 
     
     
    For Each ocell In ActiveSheet.Columns("DD").Cells 
        If Trim(ocell.Text)  "" Then 
             
             
            blah = "CS" & Trim(Str(ocell.Row)) 
            blah1 = "G" & Trim(Str(current_row)) 
             
            Sheet3.Activate 
            temp = ("Onboard") 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            Sheet10.Visible = xlSheetVisible 
             
            blah = "A" & Trim(Str(ocell.Row)) 
            blah1 = "A" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            blah = "B" & Trim(Str(ocell.Row)) 
            blah1 = "B" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            blah = "I" & Trim(Str(ocell.Row)) 
            blah1 = "C" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "J" & Trim(Str(ocell.Row)) 
            blah1 = "D" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "K" & Trim(Str(ocell.Row)) 
            blah1 = "E" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "BY" & Trim(Str(ocell.Row)) 
            blah1 = "F" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
            current_row = current_row + 1 
             
             
        End If 
         
        If ocell.Row = 2000 Then 
            bnone = False 
             
            Exit For 
        End If 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(in this report everyone who has a blank in column DD is printed on the worksheet Daily Snapshot(report). Certain information is carried over onto this sheet as well as the status Onboard.)Sheet 10 is the report worksheet and sheet 3 is the main worksheet titled "application".

I have tried putting another for loop around this for loop to add a criteria such as
For Each ocell In ActiveSheet.Columns("A").Cells
If Trim(ocell.Text) = "Accepted" Then...

but it does not work.

Any help is much appreciated!! Thanks!!

Hi all, I am trying to use either VLookup or an If statement to do the following:

I have multiple people who travel from multiple cities in Australia to one Base e.g. Brisbane, Melbourne, Sydney & Adelaide to Karratha. I want the statement to say If A2=BNE,MEL,SYD,ADL and A3=KTA then it would return a particular flight time, if not it would return the alternative.

I can't get it to do it no matter what.

Hi All

I'm struggling to work out how to count the volumes in two different colums using multiple criteria. I'm using the first column as a starting point with a singular criteria and then counting all entries in another column that conform to multiple criteria.

There's a screenshot below - what I want a formula to do is give the results in cells F5 and F10.

The criteria are in E5 and E10 respectively. E5 is saying that I want to select 'TREE' from the list in B3:B16 and then count how many times either 'MON' or 'TUE' occur in C3:C16. Likewise for E10.

Can anyone help me out with this formula?

Regards

Matt

Having trouble with a formula that will sum using multiple criteria:
if column A:A = "8/10/07" and column B:B = "Administration"
then SUM column D:D.


No luck finding an answer? You could always try Google.