Restated stumper - lookup and sum type formula needed

[vantilian]

I have spreadsheet data similar to below that changes day-to-day. There are other complexities to this spreadsheet that I will not get into here, but they prevent me from being able to sort differently, filter, etc. In summary, I need to get the results without manipulation of the spreadsheet as it presently looks:

Spreadsheet COLOR GROUP - Example Day 1

ColA ColB ColC
TOM RED 500
KEN BLUE 400
JOE RED 300
BOB WHITE 200
DAN BLUE 100
PAT WHITE 0

Spreadsheet COLOR GROUP - Example Day 2

ColA ColB ColC
PAT WHITE 500
DAN BLUE 400
KEN BLUE 300
JOE RED 200
BOB WHITE 100
TOM RED 0

I am hoping for a formula that will look at designated Color Groups (Column B) and sum all the related values from Column C. For example, a formula that says where Col B is equal to RED or BLUE accumulate values from Column C and sum. In this example Day 1's result would be 1,300; Day 2's result would be 900. Also, if the result is zero I would like it to return a numerical value 0 instead of #NA. The ulimate goal is that as the spreadsheet updates the formula recalcs the same.

Thanks in advance for reviewing, and any suggestions will be greatly appreciated.

Russell

[Paul-Johnson]

You need an array formual.

Search the archives. There is a great article on them.

Your search should be for "CSE Formulas"

[Aladin Akyurek]

On 2002-03-06 10:37, vantilian wrote:
I have spreadsheet data similar to below that changes day-to-day. There are other complexities to this spreadsheet that I will not get into here, but they prevent me from being able to sort differently, filter, etc. In summary, I need to get the results without manipulation of the spreadsheet as it presently looks:

Spreadsheet COLOR GROUP - Example Day 1

ColA ColB ColC
TOM RED 500
KEN BLUE 400
JOE RED 300
BOB WHITE 200
DAN BLUE 100
PAT WHITE 0

Spreadsheet COLOR GROUP - Example Day 2

ColA ColB ColC
PAT WHITE 500
DAN BLUE 400
KEN BLUE 300
JOE RED 200
BOB WHITE 100
TOM RED 0

I am hoping for a formula that will look at designated Color Groups (Column B) and sum all the related values from Column C. For example, a formula that says where Col B is equal to RED or BLUE accumulate values from Column C and sum. In this example Day 1's result would be 1,300; Day 2's result would be 900. Also, if the result is zero I would like it to return a numerical value 0 instead of #NA. The ulimate goal is that as the spreadsheet updates the formula recalcs the same.

Thanks in advance for reviewing, and any suggestions will be greatly appreciated. [img]/board/images/smiles/icon_smile.gif[/img]

Russell

There are alternative routes to achieve what you want such as PivotTables and Advanced Filter.

Also, different formula-based approaches: by using database functions, array-formulas, and a variant of the latter which I take up in what follows.

Suppose that A2:C7 houses the Day 1 data.

In E1 enter: RED [ a criterion/condition ]
In E2 enter: BLUE [ like above ]

In F1 enter:

=SUMIF($B$2:$B$7,$E$1,$C$2:$C$7)

This will total every cell in C associated with a B-cell whose value is RED.

In G1 enter:

=SUMPRODUCT((($B$2:$B$7=$E$1)+($B$2:$B$7=$E$2))*$C$2:$C$7)

or

=SUMPRODUCT((($B$2:$B$7=$E$1)+($B$2:$B$7=$E$2)),$C$2:$C$7) [ if you want to be kosher about the syntax of the SUMPRODUCT function ]

The latter formula totals all C-values that are associated with B-values which are either RED or BLUE.

[vantilian]

This works!

Thank you Aladin!