Numbers IF? - Multiple Dropdown Options

Hi corn,

If I am understanding you, you can get there adding SUMIF()s.

So B1 in your table (B2 in mine) would be

=SUMIF(Table 1::D,"item 2",Table 1::E)+SUMIF(Table 1::D,"item 1",Table 1::E)

Here is a formula using SUM() for D1

=SUM(SUMIF(Table 1::D,"item 3",Table 1::E),SUMIF(Table 1::D,"item 4",Table 1::E),SUMIF(Table 1::D,"item 5",Table 1::E))

quinn

How about this way, the table 2 has more cells but each has simple formula or data.

Note: count and row on Table 2 don't match with yours.

[ Table 1 ] added two footer rows for cross check

# above shows column D and E only.

count = COUNT(B)

SUM = SUM(B)

[ Table2 ] row 1,2 and column A…C are header

A3:A9 — list of items

B2 = SUM(B)

— cross check with "SUM" of Table 1

B3:B9 = SUMIF(Table 1::D,A3,Table 1::E)

C2 = SUM(C)

— cross check with "count" of Table 1

C3:C9 = COUNTIF(Table 1::D,A3)

D2 = SUMIF(D,TRUE,$B)

D3:D9 — checkboxes to determine which items to be included in the sum.

Hi c'

This looks very much like a categorization case where Item 1 and Item 2 both fit into "Category A", Items 3, 4 and 5 fit into "Category B", and Items 6 through 20 all fit into "Category C".

If that is a fixed relationship, then eseerc's first suggestion is pretty much on the mark, and needs only a means of automatically applying the Category x labels to the column he suggests adding.

Here's an example:

Main is your transaction table. Item description is listed in column D. The labels here mustbe exact matches for the set of labels in column A of Cat LU, the LookUp table used to assign category labels to each of the item descriptions.

Summary is the table containing the totals for the three categories. I've moved these totals to row 2 to allow placing the category names into the cell above the formula, then referencing that cell in the formula, making it possible to use the same formula in al three cells showing totals.

Main has one formula, entered in F2 and filled down the column. It is a Lookup formula that gets the Item description from column D, finds its match in column A of Cat LU, and returns the matching category from column B of Cat LU.

INDEX(Cat LU::B,MATCH(D2,Cat LU::A))

Cat LU contains only entered data. No formulas.

Summary contains one formula, entered in B2, filled right to F2, then deleted from cells C2 and E2:

SUMIF(Main::$F,B$1,Main::$E)

The three tables may be on the same Sheet or on different Sheets.

Regards,

Barry

Hi there,

Have you considered adding a "helper" column to Table 1? You could create a new column "F" and populate a designation for how the value should be summed. An example...

COLUMN D COLUMN E COLUMN F

Item 1 5.00 B1

Item 2 4.00 B1

Item 3 5.00 D1

Item 4 3.00 D1

Item 5 2.00 D1

Your SUMIF would then be calculated off of the values in COLUMN F instead of adding multiple SUMIFS together.

Would this work for you?

Hi again, even with this information (unless I'm not understanding clearly) I think this scenario would work. For each line item, you would need to manually assign a value to the helper column (column f) but from there your SUMIF could be based on the value in Column F instead of multiple SUMIFS based off of column A. If column E is the value you are summing, it does not matter if those numbers are static or not.

My apologies if I am not understanding correctly