Excel Formulas

Is there a formula which is similar to SUMIF that gives you the opportunity to give more than one condition? I tried AND() with SUMIF with no luck.
timjcAsked:
Who is Participating?
 
vboukharConnect With a Mentor Commented:
So you have to write user-defined-function and call it instead of SUMIF. Look at function below (for two criteria - you can add more). It's only operate with numerical or string values in columns, but you can improve it, if need.

Function MySumIf(WhatSum As Object, R1 As Object, Crit1 As String, R2 As Object, Crit2 As String) As Double
   Col1 = R1.Column
   Col2 = R2.Column
   MySumIf = 0
   For Each C In WhatSum
     If IsNumeric(Cells(C.Row, Col1)) Then
       MyCheck = Str(Cells(C.Row, Col1)) & Crit1
     Else
       TmpOper = Left(Crit1, 1)
       TmpCrit = Right(Crit1, Len(Crit1) - 1)
       MyCheck = Chr(34) & Cells(C.Row, Col1) & Chr(34) & TmpOper & Chr(34) & TmpCrit & Chr(34)
     End If
     IsCrit1 = Evaluate(MyCheck)
     If IsNumeric(Cells(C.Row, Col2)) Then
        MyCheck = Str(Cells(C.Row, Col2)) & Crit2
     Else
       TmpOper = Left(Crit2, 1)
       TmpCrit = Right(Crit2, Len(Crit2) - 1)
       MyCheck = Chr(34) & Cells(C.Row, Col2) & Chr(34) & TmpOper & Chr(34) & TmpCrit & Chr(34)
     End If
     IsCrit2 = Evaluate(MyCheck)
     If IsCrit1 And IsCrit2 Then
       MySumIf = MySumIf + C.Value
     End If
   Next
End Function
I've checked it and it works.
0
 
carttiCommented:
I've looked and looked for a solution to this problem. As yet I am still having difficulties.
0
 
aburrCommented:
Yes in a test case AND gave 0. If what you want is the sum of a column with the numbers to be summed to lie between an upper limit and a lower limit do it in a three step process. SUMIF with < the lower limit. SUMIF with < the upper limit. Subtract the first from the second. (This can be done in one cell)
-
The trouble with AND is that it returns a 0 or 1
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
timjcAuthor Commented:
I agree that this method is ok. But what if you want to do a SUMIF where you have four columns. The first column is the one that would be summed on the conditions. The other columns consist of words. The cells in the first column would only be summed if the cells on the same row in the other columns were equal to particular words.
0
 
aburrCommented:
I can do it for one column of words. three would be a bit complicated but I think it can be done. Will the word in the three columns be the same to sum the value in the first?
0
 
timjcAuthor Commented:
No, that is the problem. Basically the formula would include a figure in the first column for summing if the columns 2,3,4 equal to certain words. So as you include more columns in the criteria, fewer cells will match the specifications to allow it to become part of the sum. Here is a fictious example:

1      apple      car         phone
4      pears      van        phone
8      apple      van        chair
9      apple      car         phone

How would you write a formula that summed the numbers in column one only if col. 2 = 'apple', col.3 = 'car', and col.4 = 'phone' ? This being done without using a comparison formula for each line (e.g. IF()).
0
 
vboukharCommented:
You can add new columns with formula in every cell like that:
"=CONCATENATE(RC[-4],RC[-3],RC[-2],RC[-1])"
so you'll be able to set condition in SUMIF on this complex key field: "=SUMIF(R[-15]C:R[-1]C,"=1applecarphone")"
If it helps you, I'll repost it as answer.

0
 
dtavassoliCommented:
You can get each individual answer by :
=AND((B1="apple");(C1="car");(D1="phone"))*A1

and sum them up using the usual methods, depending on how you want to display. Each (x=y) returns a boolean, the AND returns true if all are true, the multiplication changes the boolean into a 0 or a 1 so you get :
all correct values => A1
any bad value => 0


0
 
timjcAuthor Commented:
vboukhar has already suggested a similar routine by adding an extra column to sum. I'm really looking for a formula that doesn't need an extra column of working out to give you the correct answer.
0
 
timjcAuthor Commented:
vboukhar has already suggested a similar routine by adding an extra column to sum. I'm really looking for a formula that doesn't need an extra column of working out to give you the correct answer.
0
 
timjcAuthor Commented:
Nice one!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.