Solved

Excel Formulas

Posted on 1998-11-16
277 Views
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.
0
Question by:timjc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 2
• 2
• +2

LVL 2

Expert Comment

ID: 1614480
I've looked and looked for a solution to this problem. As yet I am still having difficulties.
0

LVL 27

Expert Comment

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

Author Comment

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

LVL 27

Expert Comment

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

Author Comment

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

LVL 5

Expert Comment

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

LVL 1

Expert Comment

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

0

Author Comment

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

Author Comment

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

LVL 5

Accepted Solution

vboukhar earned 20 total points
ID: 1614489
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

Author Comment

ID: 1614490
Nice one!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with â€¦
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me â€¦
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo â€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
Suggested Courses
Course of the Month6 days, 3 hours left to enroll