Solved

Excel Formulas

Posted on 1998-11-16
11
272 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
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 2

Expert Comment

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

by:aburr
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 5

Expert Comment

by:vboukhar
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 20 total points
Comment Utility
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
Comment Utility
Nice one!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now