or statments

[thor10]

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

 

[Mark W.]

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.

 

[IML]

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

 

[Dave Patton]

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

 

[Jeebs]

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 .

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