or statments
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: or statments

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm trying to so a sumif.

    =SUM(IF(B2:B1106="MEDICAID OVERALL",if(d2:d1106='603664'or'701087'or'605424'or'724005',h2:h1106)))

    As you can see this will not work!

    Trying to add whats in column H
    if column b = MEDICAID OVERALL
    and if
    column d has any of the values listed above.

    Any help is greatly appreciated!!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this array formula...

    {=SUM((B2:B1106="MEDICAID OVERALL")*ISNUMBER(MATCH(D2:D1106,{"603664";"701087";"605424";"724005"},0))*H2:H1106)}

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It sound like your best bet may be to do a double subtotals or a pivot table. A formula answer could be
    =SUMPRODUCT(((B1:B1106)="Medicaid overall")*((D1:D1106=603664)+(D1:D1106=701087)+(D1:D1106=605424)+(D1:D1106=724005)),H1:H1106)

    good luck

    I didn't see Mark's Post. I like his better anyhoo....

    [ This Message was edited by: IML on 2002-04-03 10:23 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    try

    =SUMPRODUCT((B2:B1106="MEDICAID OVERALL")*(D2:D1106={"603664","701087","605424","724005"})*(H2:H1106))

    The info in D2:D1106 is TEXT.

    or if D2:D1106 info is actual numbers

    =SUMPRODUCT((B2:B1106="MEDICAID OVERALL")*(D2:D1106={603664,701087,605424,724005})*(H2:H1106))

  5. #5
    New Member
    Join Date
    Feb 2005
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If Statement

      
    I want to use an if statement. It's been so long since I have used one and forgot how to do it :o .

    Here is the scenario. I am creating a worksheet that calculates how much money is left on a contract. ie.

    Full Contract Amount $26,676.00
    Cost Amount Remaining
    FY 03/04
    March_2004 $469.79 $26,206.21
    April $647.25 $25,558.96
    May $587.26 $24,971.70
    June $597.25 $24,374.45
    FY 04/05
    July $587.24 $23,787.21
    August $587.26 $23,199.95
    September $653.00 $22,546.95
    October $1,251.68 $21,295.27
    November $589.72 $20,705.55
    December $589.73 $20,115.82
    January_05 $657.08 $19,458.74
    February $19,458.74
    March $19,458.74
    April $19,458.74

    The formulas is just Lets say The column to the far left is one then two then three. Well when information is typed into column two, that amount is subtracted from column three (the last dollar amount left over) I want to carry the formula down (just like i have for feb, march, and april) but I don't want the last total to show up. I only want information to appear when the dollar amount for feb is entered. and so on and so forth. Can anyone please help me out. This is driving me nuts. I have spent all day yesterday and a large portion of today trying to figure this out. I've searched and searched but cannot find an answer.

    All help is greatly appreciated

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com