Solved

Excel Formulas

Posted on 1998-11-16
11
273 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
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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 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

by:timjc
ID: 1614490
Nice one!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …

864 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

18 Experts available now in Live!

Get 1:1 Help Now