SUMIF with two conditions ?

:cool: OK I have tried just about everything and can not find the righ
combination for seems like a simple problem:

IN A1

IF N79:N83 >=1 AND
IF N79:N83 <=7

THEN SUM K79:N8

--
Calvi
-----------------------------------------------------------------------
Calvin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=590
View this thread: http://www.excelforum.com/showthread.php?threadid=26341

0
9/25/2004 3:25:03 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
557 Views

Similar Articles

[PageSpeed] 19

Hi Calvin

one way
=SUMPRODUCT(--(N79:N83>=1),--(N79:N83<=7),N79:N83)

Cheers
JulieD

"Calvin" <Calvin.1d4dfz@excelforum-nospam.com> wrote in message
news:Calvin.1d4dfz@excelforum-nospam.com...
>
> :cool: OK I have tried just about everything and can not find the right
> combination for seems like a simple problem:
>
> IN A1
>
> IF N79:N83 >=1 AND
> IF N79:N83 <=7
>
> THEN SUM K79:N83
>
>
> -- 
> Calvin
> ------------------------------------------------------------------------
> Calvin's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=5901
> View this thread: http://www.excelforum.com/showthread.php?threadid=263417
>


0
JulieD1 (2295)
9/25/2004 3:46:04 AM
Shouldn't there be an ELSE argument on this? What happens then i
N79:N83>7? Is it assumed that when N79:N83>7, cell A1 will return 
blank?

Calvin Wrote:
> :cool: OK I have tried just about everything and can not find the righ
> combination for seems like a simple problem:
> 
> IN A1
> 
> IF N79:N83 >=1 AND
> IF N79:N83 <=7
> 
> THEN SUM K79:N8

--
BenjieLo

-----------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1101
View this thread: http://www.excelforum.com/showthread.php?threadid=26341

0
9/25/2004 5:45:51 AM
Hi
as alternative to Julie's solution:
=SUMIF(N79:N83,">=1",K79:K83)-SUMIF(N79:N83,">7",K79:K83)

--
Regards
Frank Kabel
Frankfurt, Germany

"Calvin" <Calvin.1d4dfz@excelforum-nospam.com> schrieb im Newsbeitrag
news:Calvin.1d4dfz@excelforum-nospam.com...
>
> :cool: OK I have tried just about everything and can not find the
right
> combination for seems like a simple problem:
>
> IN A1
>
> IF N79:N83 >=1 AND
> IF N79:N83 <=7
>
> THEN SUM K79:N83
>
>
> --
> Calvin
> ---------------------------------------------------------------------
---
> Calvin's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=5901
> View this thread:
http://www.excelforum.com/showthread.php?threadid=263417
>

0
frank.kabel (11126)
9/25/2004 6:03:48 AM
Reply:

Similar Artilces:

Sql Server Indexing With Two or More Columns
I got a question with indexing. If I create an index and select 2 or more columns, what is the difference with that and creating 2 (or more separate ) indexes for them? Thanks mark It depends on what you are doing If you have WHERE Last=@p1 AND First=@p2 there no need to have two indexes , however having WHERE First=@p2 the first index on Last,First may or may not be useful, so in that case having two NCI may be a good idea I mean you need testing it, and make a decision.Also there is no need to create NCI on every column, it is especially true in SQL Server 2005 and onwards w...

Macro that deletes values with condition
Hi, I need a macro that deletes duplicates of numbers that appear an odd number of times and that deletes duplicates and the value duplicated an even number of times. Example: Original data A 1 2 3 1 2 1 2 3 4 Result: A 1 2 4 Values 1 and 2 must remain and only delete duplicates because they appear an odd number of times (3), 3 must be deleted because it appears an even number of times (2) and 4 appears because it has no duplicates. Hope this can be done! Thank you so much Hi Insert a heading in row 1 and try this macro: Sub aaa() Dim f As ...

CONDITION IN MACRO #2
This is an update to an earlier question I posed. I am using Access 2002. I'm trying to use a Condition in a Macro to determine whether or not to SetValue. It appears that the Condition will not allow me to use a "wildcard" (asterisk). The Field I'm checking is filled with entries like CRUZ SA or CRUZ SB or CRUZ SC. I simply want to set the Condition to look for CRUZ*...meaning any entry that begins with CRUZ. The Condition I set up is [CLASS]="CRUZ*". It doesn't work. But, when I set the Condition to [CLASS]="CRUZ SA" it works. Access Help seem...

Combining two accounts as one display
If I have two emails, can Live Mail be configured so that both accounts can be displayed (combined) For me this would prevent 2 deletes, 2 junks, etc.- Thanks. Otzi On Tue, 20 Jul 2010 18:58:21 -0700, otzi wrote: > If I have two emails, can Live Mail be configured so that both accounts can > be displayed (combined) For me this would prevent 2 deletes, 2 junks, etc.- Back in the good old days of MS Outlook Express, a number of people wanted separate account folders. Now that MS has accommodated those folk, people are demanding a global Inbox. Well, MS, in their infinite wi...

Outlook 2007
First problem: When entering a search into the search box on my Outlook email screen, no items are found. This happens no matter how positive I am I entered the search correctly and that an email exists. Second problem: When in MS Word 2007 (and I'll post this in the Word forum as well), when I go to File / Send To / Mail recipient, nothing happens. Any ideas to help solve these annoying problems? Never mind about my second problem. I got it figured out .... it's Word Perfect I'm having a problem with, not Word ! "Jan" <jan@greenwaldlaw.org> wrote in m...

more than 3 conditional formats #2
Is it possible to add more than 3 conditional formats to a particula cell? Any help would be much appreciated. Thanks, - -- Message posted from http://www.ExcelForum.com Hi A No, you'd need a macro to do the formatting with >3 conditions. HTH. Best wishes Harald "abailey >" <<abailey.18da03@excelforum-nospam.com> skrev i melding news:abailey.18da03@excelforum-nospam.com... > Is it possible to add more than 3 conditional formats to a particular > cell? > > Any help would be much appreciated. > > Thanks, > -A > > > --- > Me...

two personal file folders on OL2003 ?
I migrated from my desktop (OL2000) to a new laptop (OL2003) and have a problem. In 2003 I have two personal file folders...it appears one is for 97-2002 OL version and the other for 2003 ? I have only one pst file on the laptop. Also...both folders seem to be identical ? However...the little icon is different Where did the 2003 folder setup come from...and can I delete one or the other. When I have tried...it says you cannot delete the main mail folder ? Thanks, Tim TimR, you wrote on Thu, 2 Feb 2006 08:45:10 -0700: > I migrated from my desktop (OL2000) to a new laptop (OL2003)...

Count ifs
Hi I have a list of data shown below, in 2 cloumns. I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office...

Comparing first and last names in two lists #2
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

Stacked/clustered column chart with a two vertical axes
Much has been discussed here about how to build this chart, but I haven't found anything explaining the possibility of combining it with a second y axis. I want to graph the advertising expenditure and gross rating points (what would be the return of investment on advertising) on television for three diaper brands by day part. Day parts would be represented by the different colors of which columns will consist. There are 5 of them: day, early, prime, late and over. For each brand, I want that two data columns show. The one to the right will represent the expenditure in $ and the one to the...

Conditional Format
Hi, I am creating a conditional format which is based on a value form another cell say: if value is less than: =If(r14=14,20,30) however can I set the cell to be relative rather than one cell? Thanks You should be able to copy the cell with the conditional formatting, then Paste>Special>Formats over all the cells you wouold like to have the conditional formatting. "MS Forum Newsgroup User" wrote: > Hi, > > I am creating a conditional format which is based on a value form another > cell say: > > if value is less...

Please help with Conditional Formatting
Hi all, I am trying to get a cell to change color when: D5 contains "/" in the form of for example: CM/F/CM or C/C I tried different variations of: Formula Is------------------$D$5= "*/*" but it does not work, any ideas? Thanks for nay help, Emilio Hi! Try this: Formula Is: =ISNUMBER(SEARCH("/",A1)) Biff >-----Original Message----- >Hi all, > >I am trying to get a cell to change color when: > D5 contains "/" in the form of for example: > >CM/F/CM or C/C > >I tried different variations of: > >Formula Is...

Adjusting Two Columns with Text
I have two columns. I've entered numbered text in each column. As I add more numbered items, the second column keeps adjusting itself downward. Is there anyway to freeze the second column as I add items to column one? I am not really sure what you mean by "the second column keeps adjusting itself downward". Do you have a two column table, or is the document formatted with columns? It sounds like you may have the former when what you want is the latter. Format the document so that it has two columns (via the Page Setup section of the Page Layout tab in Word 200...

Outlook Two mail accounts
I have a network whith Win2000 server and exchange 2000 server my question is after I configure the clients whith Outlook I add one account POP 3 and one Exchange account in each client I make the POP3 account default, no problem if I make a new e-mail Outlook send it whith the POP3 account but If I reply or foward a -email the program try to send it whith Exchange , the problem is my exchange is just for internal use in the office is not for internet mail so How can I change that behavior to Outlook always send and receive whith the POP3 account and use the Exchange account just for...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

Record data on two lines
Sorry about repeating the post but I think I messed up the first one yeaterday! I am receiving a spreadsheet from an agency with record information on two lines. Example: Record 1: A1, B1, C1, D1, A2, B2, C2 Line 3 blank Record 2: A4, B4, C4, D4, A5, B5, C5 There are over 100 records like this. They claim they can't fix it... It originates from a Crystal Report. How can I change the data so each record is on one line: Record 1: A1, B1, C1, D1, E1, F1, G1 Record 2: A2, B2, .......................G2 Thanks for any help! Dan A simple way is to 1) select all of the data fields. 2) Cli...

how do I create a chart on two axes with stacked columns and line
I am struggling to create a chart that combines stacked columns on one X axis with a line on a second axis. Both are available as standard charts. Hi, Create the stacked column chart with all of your data. Select the data series you want to plot as a line on the secondary axis. Change the chart type to line. And then on the format dialog change the axis to the secondary. Cheers Andy ian ian ian rogers wrote: > I am struggling to create a chart that combines stacked columns on one X axis > with a line on a second axis. Both are available as standard charts. -- Andy Pope, Microso...

Conditional Format? #2
Excel 2000 ... I wish to Conditional Format merged Cells based on the 1st character (Alpha or numeric) appearing in the Cell. How do I achieve this? ie: Monthly ... would format based on the "M" Thanks ... Kha Hi Ken Select the cells concerned and choose Format=>Conditional Formatting select drop down for Formula Is and in the white pane type =LEFT(B4)="M" Choose the Format you require Note the cell reference e.g. B4 must be the top left cell of your range of Merged cells -- Regards Roger Govier "Ken" <anonymous@discussions.microsoft.com> wrot...

Conditional Formating a Textbox in report...
I have a report that I want to change any empty fields (textboxes) to have a red background. I cannot seem to get backgound colors to work? If I set conditional formatting on a value < 10 with text color changing it seems to work. Here's my 2 attempts: Len([NumberOfReps])<1 Or IsNull([NumberOfReps]) ---set background to red Then I tried this in the OnFormat event of the Detail section: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Not Len(Me.NumberofReps) >= 1 Then Me.NumberofReps.BackColor = vbRed End If End Sub Background colors...

Complicated conditional "countif" formula
Hi. I have several columns labeled "Monitoring Visit 1...Monitoring Visit 8", but interspersed between them I have columns labeled "Report Date", with each report being associated with a given Monitoring visit. In the last column, on each row, I want to count the number of cells with dates in them, which signifies if a monitoring visit has occurred or not. I can't do a normal count if, because of the extra Report columns in between, which have stay where they are. Can anyone lead me to the right formula? Thanks. =SUMPRODUCT(--(LEFT(A$1:O$1,16)="Monitoring Vi...

Excel
Help, my formulas does not seem to calculate negative numbers. If a= 91 then result is 30 days. =if(a<-90,"-90days",if(a<-60,"-60days","30 days")) -- TTB Thanks! TTB, You need to give the formula a complete cell reference A1, or A2, not just A. =if(a1<-90,"-90days",if(a1<-60,"-60days","30 days")) Also if you want -90 to return "-90days", and -60 to return "-60days", then you will need to replace "<" with "<=" like this: =if(a<=-90,"-90days"...

Conditional Formating (how to use Offset() in cell reference)
Using XL 2003 & 97 How do enter into VBA code; the cell Offset R-12,C into the Contitional Formatting below? (I want the "not equal to" referrence a cell 12 rows above in the same column) Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=$J$6397" TIA Dennis This might help you get to the next step: Option Explicit Sub testme01() With Selection If .Row < 13 Then 'too high in the worksheet Else 'remove any existing format first????? .FormatConditions.Delete .FormatConditions.Add ...

SUMIF using 2 cells that equal each other
I am using SUMIF to add cells in a column but the criteria I want to use is add the cell only if the criteria equals another cell. I do not want to enter the actual variable of the cell as it changes for each row of column C. As an example: =SUMIF('A2:A5001,"C2",D2:D5001) where "C2" equals a variable that can change. Is there any function I can use for the variable? You almost had it, try this =SUMIF(A2:A5001,C2,D2:D5001) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while sti...

Merging two newsletters?
How can I merge two (or more) newsletters to make one? Christy Y wrote: > How can I merge two (or more) newsletters to make one? ============================== Open two instances of Publisher... one document in each one... Tile the two instances on your screen... Copy / Paste...from one to the other... And...maybe the following link will be useful: Combining Publisher Documents http://msauer.mvps.org/combining_publisher_documents.htm -- John Inzer Digital Media MVP Notice This is not tech support I am a volunteer Solutions that work for me may not work f...

Merging two lists #2
I need to merge two address lists with differing information. Some addresses in list A are also in list B. List B includes a column with phone numbers and A does not. How do I merge the two adding the phone number to the correct address rows and keep all the other info intact? Hi Bill so all the addresses are in list A, but list B has additional information that list A doesn't have? if so you can use the VLOOKUP to "add" the data from list B to list A (assuming your spreadsheet is set up in a useable format) e.g. list A .....A............B 1..Name....Address list B .....