[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel Formulas

Posted on 1998-11-16
11
Medium Priority
?
281 Views
Last Modified: 2008-03-17
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
Comment
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
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 2

Expert Comment

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

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

by:timjc
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 27

Expert Comment

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

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

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

by:dtavassoli
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
any bad value => 0


0
 

Author Comment

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

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

by:
vboukhar earned 80 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

by:timjc
ID: 1614490
Nice one!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question