using more than one criteria???

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

is it possible to use a Function with more than one Criteria in the formula?
if so what functions can, please give an example.

Many thanks,

Steve
 
Are you talking about SUMIF and COUNTIF? These work only with a
single criterion. There are ways to write formulas that work with
multiple criteria, namely array formulas and SUMPRODUCT. You
might want to post back with more details about what exactly you
want to do.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
I need to search rows like this, to find the time value from D, matching A and B criteria


A B C D E
Cause Pay No Name Mon H:M Tue H:M
AB 123 Smith 05:00
BB 321 Jones 03:00





searching google i have found this code, iv just added it to my WB, not sure if it is what i need?
--------------------------------------------------------------------------------------------------------------
The Excel function SUMIF only allows you to specific and single criteria for the summation. The following code allows you to specify two criterias:

Option Explicit
Option Compare Text


'Purpose : Copies the selected items in a listview to the clipboard
'Inputs : Range1 The first range to evaluate.
' vCriteria1 The criteria to apply to the first range.
' sEvaluation1 Can be:
' "=" Checks the values of the cells in Range1 are equal to vCriteria1.
' "<" Checks the values of the cells in Range1 are less than vCriteria1.
' "<=" Checks the values of the cells in Range1 are less than or equal to vCriteria1.
' ">=" Checks the values of the cells in Range1 are greater than or equal to vCriteria1.

' Range2 The second range to evaluate.
' vCriteria2 The criteria to apply to the second range.
' sEvaluation2 See sEvaluation1
'Outputs : Returns the sum of the values in SumRange which meet the two criterias
'Author : Andrew Baker
'Date : 04/Sep/2002
'Notes : An extended version of SUMIF which allows you to specify two criterias.
'Revisions :

Function SUMIFEX(Range1 As Excel.Range, vCriteria1 As Variant, sEvaluation1 As String, Range2 As Excel.Range, vCriteria2 As Variant, sEvaluation2 As String, SumRange As Excel.Range) As Variant
Dim colMatching1 As Collection, colMatching2 As Collection, oCell As Excel.Range

Application.Volatile True
Set colMatching1 = New Collection: Set colMatching2 = New Collection

'Find the matching values in the first range
For Each oCell In Range1
Select Case sEvaluation1
Case "="
If oCell.Value = vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case "<"
If oCell.Value < vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case "<="
If oCell.Value <= vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case ">"
If oCell.Value > vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case ">="
If oCell.Value >= vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
End Select
Next

'Find the matching values in the second range
For Each oCell In Range2
Select Case sEvaluation2
Case "="
If oCell.Value = vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case "<"
If oCell.Value < vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case "<="
If oCell.Value <= vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case ">"
If oCell.Value > vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case ">="
If oCell.Value >= vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
End Select
Next

'Sum the values which are in both ranges
On Error Resume Next
For Each oCell In SumRange
If Len(colMatching1.Item(CStr(oCell.Row))) = 0 Or Len(colMatching2.Item(CStr(oCell.Row))) = 0 Then
Else
'Value is in both ranges
SUMIFEX = SUMIFEX + oCell.Value
End If
Next
Set colMatching1 = Nothing: Set colMatching2 = Nothing
End Function
 
If I understand what you're asking, try this.

Column A criteria in J1
Column B criteria in K1
Enter this in a cell formatted for time:

=SUMPRODUCT((A2:A50=J1)*(B2:B50=K1)*D2:D50)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I need to search rows like this, to find the time value from D, matching A
and B criteria


A B C D E
Cause Pay No Name Mon H:M Tue H:M
AB 123 Smith 05:00
BB 321 Jones 03:00





searching google i have found this code, iv just added it to my WB, not sure
if it is what i need?
----------------------------------------------------------------------------
----------------------------------
The Excel function SUMIF only allows you to specific and single criteria for
the summation. The following code allows you to specify two criterias:

Option Explicit
Option Compare Text


'Purpose : Copies the selected items in a listview to the clipboard
'Inputs : Range1 The first range to evaluate.
' vCriteria1 The criteria to apply to the first
range.
' sEvaluation1 Can be:
' "=" Checks the values of the cells
in Range1 are equal to vCriteria1.
' "<" Checks the values of the cells
in Range1 are less than vCriteria1.
' "<=" Checks the values of the cells
in Range1 are less than or equal to vCriteria1.
' ">=" Checks the values of the cells
in Range1 are greater than or equal to vCriteria1.

' Range2 The second range to evaluate.
' vCriteria2 The criteria to apply to the second
range.
' sEvaluation2 See sEvaluation1
'Outputs : Returns the sum of the values in SumRange which meet the two
criterias
'Author : Andrew Baker
'Date : 04/Sep/2002
'Notes : An extended version of SUMIF which allows you to specify two
criterias.
'Revisions :

Function SUMIFEX(Range1 As Excel.Range, vCriteria1 As Variant, sEvaluation1
As String, Range2 As Excel.Range, vCriteria2 As Variant, sEvaluation2 As
String, SumRange As Excel.Range) As Variant
Dim colMatching1 As Collection, colMatching2 As Collection, oCell As
Excel.Range

Application.Volatile True
Set colMatching1 = New Collection: Set colMatching2 = New Collection

'Find the matching values in the first range
For Each oCell In Range1
Select Case sEvaluation1
Case "="
If oCell.Value = vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case "<"
If oCell.Value < vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case "<="
If oCell.Value <= vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case ">"
If oCell.Value > vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case ">="
If oCell.Value >= vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
End Select
Next

'Find the matching values in the second range
For Each oCell In Range2
Select Case sEvaluation2
Case "="
If oCell.Value = vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case "<"
If oCell.Value < vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case "<="
If oCell.Value <= vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case ">"
If oCell.Value > vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case ">="
If oCell.Value >= vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
End Select
Next

'Sum the values which are in both ranges
On Error Resume Next
For Each oCell In SumRange
If Len(colMatching1.Item(CStr(oCell.Row))) = 0 Or
Len(colMatching2.Item(CStr(oCell.Row))) = 0 Then
Else
'Value is in both ranges
SUMIFEX = SUMIFEX + oCell.Value
End If
Next
Set colMatching1 = Nothing: Set colMatching2 = Nothing
End Function

----------------------------------------------------------------------------
 
Steven, to find the value from D where that value matches criteria from A and B, you can use a helper column and two input cells. Assume F is the helper column and I2 and J2 are the input cells. In F2, enter this formula: =IF(AND(A2=$I$2,B2=$J$2),D2,"No Match"), copy the formula down the column and format the cells for time. Enter criteria in the input cells I2 and J2 where I2=a value from A and J2=a value from B and formula in Column F will return values from D where criteria match. If you need to isolate these rows, use AutoFilter where Column F <> "No Match."

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
I need to search rows like this, to find the time value from D, matching A and B criteria


A B C D E
Cause Pay No Name Mon H:M Tue H:M
AB 123 Smith 05:00
BB 321 Jones 03:00





searching google i have found this code, iv just added it to my WB, not sure if it is what i need?
--------------------------------------------------------------------------------------------------------------
The Excel function SUMIF only allows you to specific and single criteria for the summation. The following code allows you to specify two criterias:

Option Explicit
Option Compare Text


'Purpose : Copies the selected items in a listview to the clipboard
'Inputs : Range1 The first range to evaluate.
' vCriteria1 The criteria to apply to the first range.
' sEvaluation1 Can be:
' "=" Checks the values of the cells in Range1 are equal to vCriteria1.
' "<" Checks the values of the cells in Range1 are less than vCriteria1.
' "<=" Checks the values of the cells in Range1 are less than or equal to vCriteria1.
' ">=" Checks the values of the cells in Range1 are greater than or equal to vCriteria1.

' Range2 The second range to evaluate.
' vCriteria2 The criteria to apply to the second range.
' sEvaluation2 See sEvaluation1
'Outputs : Returns the sum of the values in SumRange which meet the two criterias
'Author : Andrew Baker
'Date : 04/Sep/2002
'Notes : An extended version of SUMIF which allows you to specify two criterias.
'Revisions :

Function SUMIFEX(Range1 As Excel.Range, vCriteria1 As Variant, sEvaluation1 As String, Range2 As Excel.Range, vCriteria2 As Variant, sEvaluation2 As String, SumRange As Excel.Range) As Variant
Dim colMatching1 As Collection, colMatching2 As Collection, oCell As Excel.Range

Application.Volatile True
Set colMatching1 = New Collection: Set colMatching2 = New Collection

'Find the matching values in the first range
For Each oCell In Range1
Select Case sEvaluation1
Case "="
If oCell.Value = vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case "<"
If oCell.Value < vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case "<="
If oCell.Value <= vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case ">"
If oCell.Value > vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case ">="
If oCell.Value >= vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
End Select
Next

'Find the matching values in the second range
For Each oCell In Range2
Select Case sEvaluation2
Case "="
If oCell.Value = vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case "<"
If oCell.Value < vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case "<="
If oCell.Value <= vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case ">"
If oCell.Value > vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case ">="
If oCell.Value >= vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
End Select
Next

'Sum the values which are in both ranges
On Error Resume Next
For Each oCell In SumRange
If Len(colMatching1.Item(CStr(oCell.Row))) = 0 Or Len(colMatching2.Item(CStr(oCell.Row))) = 0 Then
Else
'Value is in both ranges
SUMIFEX = SUMIFEX + oCell.Value
End If
Next
Set colMatching1 = Nothing: Set colMatching2 = Nothing
End Function
 
With your dates entered down column A,
Enter the starting date of your range in C1,
And the ending date of your range in D1.

Either on of these formulas should return the number of entries in columnA
that are defined by C1 and D1:

=SUMPRODUCT((A1:A50>=C1)-(A1:A50>D1))

=COUNTIF(A1:A50,">="&C1)-COUNTIF(A1:A50,">"&D1)

Note:
The entries in C1 and D1 are considered as part of the range, and *are*
counted,
The dates in column A do *not* have to be in any order,
All date entries should be *true* dates.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I am actually interested in doing the same thing...
I have a column of dates in the format xx/xx/xxxx

I want to get a count of how many cells fall between a
given date range so that I can get a total by month.

A reply to my e-mail would be great, if possible.

Thanks in advance...

Jeff
 
Thanks everyone for your help

RagDyer said:
If I understand what you're asking, try this.

Column A criteria in J1
Column B criteria in K1
Enter this in a cell formatted for time:

=SUMPRODUCT((A2:A50=J1)*(B2:B50=K1)*D2:D50)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I need to search rows like this, to find the time value from D, matching A
and B criteria


A B C D E
Cause Pay No Name Mon H:M Tue H:M
AB 123 Smith 05:00
BB 321 Jones 03:00





searching google i have found this code, iv just added it to my WB, not sure
if it is what i need?
-------------------------------------------------------------------------- --
----------------------------------
The Excel function SUMIF only allows you to specific and single criteria for
the summation. The following code allows you to specify two criterias:

Option Explicit
Option Compare Text


'Purpose : Copies the selected items in a listview to the clipboard
'Inputs : Range1 The first range to evaluate.
' vCriteria1 The criteria to apply to the first
range.
' sEvaluation1 Can be:
' "=" Checks the values of the cells
in Range1 are equal to vCriteria1.
' "<" Checks the values of the cells
in Range1 are less than vCriteria1.
' "<=" Checks the values of the cells
in Range1 are less than or equal to vCriteria1.
' ">=" Checks the values of the cells
in Range1 are greater than or equal to vCriteria1.

' Range2 The second range to evaluate.
' vCriteria2 The criteria to apply to the second
range.
' sEvaluation2 See sEvaluation1
'Outputs : Returns the sum of the values in SumRange which meet the two
criterias
'Author : Andrew Baker
'Date : 04/Sep/2002
'Notes : An extended version of SUMIF which allows you to specify two
criterias.
'Revisions :

Function SUMIFEX(Range1 As Excel.Range, vCriteria1 As Variant, sEvaluation1
As String, Range2 As Excel.Range, vCriteria2 As Variant, sEvaluation2 As
String, SumRange As Excel.Range) As Variant
Dim colMatching1 As Collection, colMatching2 As Collection, oCell As
Excel.Range

Application.Volatile True
Set colMatching1 = New Collection: Set colMatching2 = New Collection

'Find the matching values in the first range
For Each oCell In Range1
Select Case sEvaluation1
Case "="
If oCell.Value = vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case "<"
If oCell.Value < vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case "<="
If oCell.Value <= vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case ">"
If oCell.Value > vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
Case ">="
If oCell.Value >= vCriteria1 Then
colMatching1.Add oCell.Value, CStr(oCell.Row)
End If
End Select
Next

'Find the matching values in the second range
For Each oCell In Range2
Select Case sEvaluation2
Case "="
If oCell.Value = vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case "<"
If oCell.Value < vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case "<="
If oCell.Value <= vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case ">"
If oCell.Value > vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
Case ">="
If oCell.Value >= vCriteria2 Then
colMatching2.Add oCell.Value, CStr(oCell.Row)
End If
End Select
Next

'Sum the values which are in both ranges
On Error Resume Next
For Each oCell In SumRange
If Len(colMatching1.Item(CStr(oCell.Row))) = 0 Or
Len(colMatching2.Item(CStr(oCell.Row))) = 0 Then
Else
'Value is in both ranges
SUMIFEX = SUMIFEX + oCell.Value
End If
Next
Set colMatching1 = Nothing: Set colMatching2 = Nothing
End Function

-------------------------------------------------------------------------- --
-------------------------------

Many Thanks,

Steve
Click to expand...
 
Back
Top