Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 2nd 06, 11:28 PM posted to microsoft.public.excel.worksheet.functions
choc_penguin
 
Posts: n/a
Default summing values appearing in col B when col A has been filtered


In sheet 1 I have two columns:

Column A has a list of 10 entries of 4 different people, randomly
repeating.

(Bob, Sally, Bob, Dave, Sarah, Sally, Sarah, Bob, Sally, Bob)

Column B has random values for each entry

(10, 13, 50, 56, 100, 250, 20, 35, 60, 25)

I have applied the autofilter so that I am able to see the 'values' for
each person.

In sheet 2 I have two columns:

Column A with 4 entries of the 4 people sheet 1 is concerned with.

(Bob, Sally, Dave, Sarah)

In column B of sheet 2, I want totals to appear for each person by
summing the values that appear for each in sheet 1. (i.e. 120 for Bob)

I'm thinking it's got something to do with vlookup but that's as far as
i got!

Any help is much appreciated!

Thanks


--
choc_penguin
------------------------------------------------------------------------
choc_penguin's Profile: http://www.excelforum.com/member.php...o&userid=30099
View this thread: http://www.excelforum.com/showthread...hreadid=507936


  #2   Report Post  
Old February 2nd 06, 11:41 PM posted to microsoft.public.excel.worksheet.functions
Pete
 
Posts: n/a
Default summing values appearing in col B when col A has been filtered

You want to sum using one criteria, so SUMIF can be used here - in B1
of Sheet 2 enter this formula:

=SUMIF('Sheet 1'!A1:A10,A1,'Sheet 1'!B1:B10)

Copy the formula into B2 to B4.

Hope this helps.

Pete

  #3   Report Post  
Old February 2nd 06, 11:54 PM posted to microsoft.public.excel.worksheet.functions
choc_penguin
 
Posts: n/a
Default summing values appearing in col B when col A has been filtered


Was just about to reply saying I'd figured it out but Thanks for your
reply!




--
choc_penguin
------------------------------------------------------------------------
choc_penguin's Profile: http://www.excelforum.com/member.php...o&userid=30099
View this thread: http://www.excelforum.com/showthread...hreadid=507936

  #4   Report Post  
Old February 3rd 06, 11:18 AM posted to microsoft.public.excel.worksheet.functions
Kevin Vaughn
 
Posts: n/a
Default summing values appearing in col B when col A has been filtered

=SUMPRODUCT(--(Data!$A$1:$A$10=A38),--(Data!$B$1:$B$10))
Change Data to Sheet1
--
Kevin Vaughn


"choc_penguin" wrote:


In sheet 1 I have two columns:

Column A has a list of 10 entries of 4 different people, randomly
repeating.

(Bob, Sally, Bob, Dave, Sarah, Sally, Sarah, Bob, Sally, Bob)

Column B has random values for each entry

(10, 13, 50, 56, 100, 250, 20, 35, 60, 25)

I have applied the autofilter so that I am able to see the 'values' for
each person.

In sheet 2 I have two columns:

Column A with 4 entries of the 4 people sheet 1 is concerned with.

(Bob, Sally, Dave, Sarah)

In column B of sheet 2, I want totals to appear for each person by
summing the values that appear for each in sheet 1. (i.e. 120 for Bob)

I'm thinking it's got something to do with vlookup but that's as far as
i got!

Any help is much appreciated!

Thanks


--
choc_penguin
------------------------------------------------------------------------
choc_penguin's Profile: http://www.excelforum.com/member.php...o&userid=30099
View this thread: http://www.excelforum.com/showthread...hreadid=507936




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Unique Values In A Filtered Row with Duplicates jcpotwor Excel Discussion (Misc queries) 1 January 13th 06 01:02 AM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 02:03 AM
Summing values if conditions are true mike camburn Excel Worksheet Functions 2 December 12th 05 09:38 PM
What is the best method for summing values where you want 2 value. Susan F. Excel Discussion (Misc queries) 2 March 25th 05 07:03 PM
Second serie doesn't use X-as values JackRnl Charts and Charting in Excel 1 January 20th 05 01:04 AM


All times are GMT +1. The time now is 06:17 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017