Restated stumper - lookup and sum type formula needed

vantilian

New Member
Joined
Mar 3, 2002
Messages
44
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You need an array formual.

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

Your search should be for "CSE Formulas"
 
Upvote 0
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. /board/images/smiles/icon_smile.gif

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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top