can lookup be used for two criteria and combined with sum

**miksnake** · 05-30-2012, 07:11 PM

In my spreadsheet I have three columns. The first is an ID number, the second is a category and the third is a value. I would like to sum all items that match both the ID and a category.

For example, say I have the following data in columns A, B, C:

A, B, C
101, large, 10
101, large, 5
101, small, 8
101, curly, 3
101, small, 11
101, round, 14
102, curly, 2
102, large, 10
102, large, 5
102, round, 8
102, curly, 3
102, small, 11
102, huge, 14
102, round, 2

So, I would like to find all the lines with ID 101 and large and sum the results from column 3. So, for that lookup, the result would be 15 (it would add the first two lines which both match 101 and large).

Is this possible?

Thanks in advance.

**jeffreybrown** · 05-30-2012, 07:16 PM

Hello & Welcome to the Forum,

For 2003 you can use
=SUMPRODUCT(--($A$1:$A$14=101),--($B$1:$B$14="Large"),$C$1:$C$14)

or

=SUMPRODUCT(--($A$1:$A$14=$E$1),--($B$1:$B$14=$F$1),$C$1:$C$14)

where E1 = 101 and F1 = Large

For 2007+
=SUMIFS($C$1:$C$14,$A$1:$A$14,101,$B$1:$B$14,"Large")

or

=SUMIFS($C$1:$C$14,$A$1:$A$14,$E$1,$B$1:$B$14,$F$1)

where E1 = 101 and F1 = Large

**Palmetto** · 05-30-2012, 07:42 PM

Two additional options:

1. Pivot Table (recommended) - offers the greatest flexibility and ease to change criteria
2. SUBTOTAL function used in conjunction with auto-filter.