Can I use more than 7 if statements somehow?

[Tom J]

I am trying to create a list that has more than seven fields that I need to
align with another column. I am trying to make it so when I type in one thing
other columns fill in the appropriate information. Can anyone help me with an
alternative solution?

 

[Bob Phillips]

No but you might be able to do it another way.

Post details of the problem.

 

[Dee]

I had a similiar problem last week. Here are the posts to
that with various solutions. Maybe one will work for you.

Dee
Reston, VA

Subject: Sum based on various criteria
From: "Dee" <[email protected]> Sent:
9/24/2004 1:07:51 PM

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

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(C12,$H$11:$I$17,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




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.




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