Sum based on various criteria

[Dee]

I have a list of outstanding invoice balances that each
have a status. Depending on what the status is, I want to
copy the balance into one of two other columns. I have a
nested if statement, but now I have over seven status
types, which the if statement can't handle.

A B C D E
Invoice # Invoice Bal Status Will Pay Won't pay
123456 10,000.00 pay in 7 days 10,000.00 0.00
789012 500.00 refused 0.00 500.00

Thanks for your help,
Dee
Reston, VA

 

[Frank Kabel]

Hi
maybe the following is what you're looking for:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Hi Dee,

When nested IF functions get too long, it's usually best to set up a table
and use the VLookup function. For instance, if the Status table was located
in H1:I7

... H I
1 Refused Won't Pay
2 7 Days Will Pay
3 Status3 Will Pay
4 Status4 Won't Pay
5 etc...
6
7

Then in column D your formulas would look like this:
=VLOOKUP(C2,$H$1:$I$7,2,FALSE)

Lastly, to get your totals, use these formulas in any two cells
=SUMIF(D2100,"Will Pay",C2:C100) and =SUMIF(D2100,"Won't Pay",C2:C100)
where 100 is the number of rows

 

[Dee]

No, I don't think this works. It only tests for one
criteria in different columns. I have multiple types in
one column. I need something like:

in cell D2: SUMIF(C2="pay in 7 days" OR C2="pmt in mail"
OR C2="xxx",B2)

in cell E2: SUMIF(C2="refused" OR C2="problem" OR
C2="yyy",B2)

Dee
Reston, VA

-----Original Message-----
Hi
maybe the following is what you're looking for:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

A B C D E
Invoice # Invoice Bal Status Will Pay Won't pay
123456 10,000.00 pay in 7 days 10,000.00 0.00
990 1,000.00 pmt in mail 1,000.00 0.00
789012 500.00 refused 0.00 500.00
8891 200.00 problem 0.00 200.00

 

[Frank Kabel]

Hi
try for example:
=SUMIF(C2:C100,"pay in 7 days",B2:B100)+SUMIF(C2:C100,"pmt in
mail",B2:B100)+SUMIF(C2:C100,"xxx",B2:B100)
or
=SUMPRODUCT((C2:C100={"pay in 7 days","pmt in mail","xxx"})*(B2:B100))

 

[Aladin Akyurek]

One of:

=SUM(SUMIF($C$2:$C$200,{"pay in 7 days","pmt in mail","xxx"},$B$2:$B$200)

=SUMPRODUCT(--ISNUMBER(MATCH($C$2:$C$200,{"pay in 7 days","pmt in
mail","xxx"},0)),$B$2:$B$200)

Apply the same logic to the second set of criteria.