Merging Data based on Criteria

[renee35]

I am trying to sum data based on a specified criteria. The data below represents what I am trying to accomplish. What I need is to sum column B based on column A having the same value. If the value in column A is not the same, then return the value in column B.

A B
1 8
2 4
3 9
3 15
4 6
5 8
5 14
5 20

Any help would be greatly appreciated!!!

Thanks a bunch!!

-T

 

[SkipVought]

Hi,

Check out the PivotTable Wizard.

Data > PivotTable...

But before you try, your columns must each have a unique heading.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[renee35]

Thanks for the suggestion, however I currently have a Pivottable created. I need the data summed before pulling it into the table because I only want to pull in values over a certain dollar amount? I tried the SUMIF function, but not sure how the criteria part would be written?

Thanks a bunch!!

-T

 

[GlennUK]

I only want to pull in values over a certain dollar amount

Before or after the summing? Could you give an example?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

Confused - why would you not just use the Pivot table features to exclude the data you don't want.

You could also simply add a 3rd column, and use something like =IF(A2=B2,A2,B2) if what you want is one of the two values no matter what, OR, if what you are actually looking for is the sum of ONLY the data where A=B, then just add a 3rd column, and use =A2=B2 and copy down. Now just throw that field into the page fields and filter on TRUE or FALSE depending on what you want.

Just not 100% sure what it is you are after.

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[renee35]

GlennUK

what I mean by values over a certain $ amnt is: anything over 1 million I don't want listed in the report?

For example, in the list above if column A "5" three rows added up to be 1 million or more that value shouldn't appear. This is after summing the like values.

Thanks a bunch!!

-T

 

[renee35]

Ken,

Thanks for your response, however I only need the data to be summed if column A has the same value listed more than once. In the example, there are 3 rows where the value for column A is "5". I need those 3 rows added based on them having the same value in column "A". Sorry for the confusion.

Thanks a bunch!!

-T

 

[SkipVought]

It would be MUCH easier to comprehend, if you posted a concrete example, illustrating your dilemma.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[combo]

With additional column in pivot table (assumed row field is 'A', data field is 'B' with
'Sum' as domain function):
- add calculation field to the pivot table and formula: =(B >1000000). As a result you should get
another column with values either 0 (out of condition) or 1 (matched),
- in field 'A' advanced settings choose 'show top' and set the value to '1'.
Due to recalculation rules, you need both 'B' as data field and calculation field after 'B'.

combo

 

[thegrover]

I think you should try using the "SUMPRODUCT" function.
If you need an example let me know.

Steve

 

[yogia]

Hi renee35:

How about posting the expected result(s) for the data in your original post ... along with an explanation as to why that is the correct result -- that would help the viewers in clearly seeing what you are trying to accomplish.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com