Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mik Mik is offline
external usenet poster
 
Posts: 42
Default Sum data, depending on cell content

Hello,

I am looking for some assistance.
Please see the following example:-

Col A Col B Col C Col D
1 apple 0.10 0.10
2 pear 0.15 0.30
orange 0.15
3 banana 0.20 0.20
4 kiwi 0.22 0.22

Lets assume that Column A is a receipt number, and Column B is product
purchased, and Column C is the unit cost, and Column D is the total
cost for that particular receipt.

I want to sum the applicable contents of column C, depending on the
value of Column A.

This I find simple, however I have the following problem.

If somebody purchases multiple items, i wish to display the receipt
Number only once for their transaction, and will not be displayed
against each item they purchased.
So, as per the following example, receipt 2 (Column A) contained 2
purchases each costing 0.15 (x2).
So, i want to show the sum of 0.15 + 0.15 in the appropriate row of
Column D

The purchaser could buy any number of items, so how do i sum this
value, when he could buy anything between 1 or 10 items?

Thanks in advance,

Mike

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Sum data, depending on cell content

Assume that you have a header row (row1). Also assume that you enter
the receipt number in column A for every item bought (so you would not
have a blank next to orange in your example) and that multiple items
on the same receipt are in consecutive rows. Then you can put this
formula in D2:

=IF(A2=A1,"",SUMIF(A$2:A$100,A2,C$2:C$100))

Copy the formula down as required. I've assumed 100 rows of data -
adjust to suit.

Hope this helps.

Pete

On Oct 18, 11:46 am, Mik wrote:
Hello,

I am looking for some assistance.
Please see the following example:-

Col A Col B Col C Col D
1 apple 0.10 0.10
2 pear 0.15 0.30
orange 0.15
3 banana 0.20 0.20
4 kiwi 0.22 0.22

Lets assume that Column A is a receipt number, and Column B is product
purchased, and Column C is the unit cost, and Column D is the total
cost for that particular receipt.

I want to sum the applicable contents of column C, depending on the
value of Column A.

This I find simple, however I have the following problem.

If somebody purchases multiple items, i wish to display the receipt
Number only once for their transaction, and will not be displayed
against each item they purchased.
So, as per the following example, receipt 2 (Column A) contained 2
purchases each costing 0.15 (x2).
So, i want to show the sum of 0.15 + 0.15 in the appropriate row of
Column D

The purchaser could buy any number of items, so how do i sum this
value, when he could buy anything between 1 or 10 items?

Thanks in advance,

Mike



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mik Mik is offline
external usenet poster
 
Posts: 42
Default Sum data, depending on cell content

On 18 Oct, 12:56, Pete_UK wrote:
Assume that you have a header row (row1). Also assume that you enter
the receipt number in column A for every item bought (so you would not
have a blank next to orange in your example) and that multiple items
on the same receipt are in consecutive rows. Then you can put this
formula in D2:

=IF(A2=A1,"",SUMIF(A$2:A$100,A2,C$2:C$100))

Copy the formula down as required. I've assumed 100 rows of data -
adjust to suit.

Hope this helps.

Pete

On Oct 18, 11:46 am, Mik wrote:



Hello,


I am looking for some assistance.
Please see the following example:-


Col A Col B Col C Col D
1 apple 0.10 0.10
2 pear 0.15 0.30
orange 0.15
3 banana 0.20 0.20
4 kiwi 0.22 0.22


Lets assume that Column A is a receipt number, and Column B is product
purchased, and Column C is the unit cost, and Column D is the total
cost for that particular receipt.


I want to sum the applicable contents of column C, depending on the
value of Column A.


This I find simple, however I have the following problem.


If somebody purchases multiple items, i wish to display the receipt
Number only once for their transaction, and will not be displayed
against each item they purchased.
So, as per the following example, receipt 2 (Column A) contained 2
purchases each costing 0.15 (x2).
So, i want to show the sum of 0.15 + 0.15 in the appropriate row of
Column D


The purchaser could buy any number of items, so how do i sum this
value, when he could buy anything between 1 or 10 items?


Thanks in advance,


Mike- Hide quoted text -


- Show quoted text -


Thanks for your responce Pete,

Is there a way of applying this without having to insert the receipt
number for all transactions?

Mike

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Sum data, depending on cell content

I can't think of a way - SUMIF relies on matching data from the range
in its first parameter to the contents of its second parameter, so if
you had blanks in column A it would not be able to match with it.

Is there a problem recording the receipt number on every row?

Pete

On Oct 18, 1:04 pm, Mik wrote:

Thanks for your responce Pete,

Is there a way of applying this without having to insert the receipt
number for all transactions?

Mike



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mik Mik is offline
external usenet poster
 
Posts: 42
Default Sum data, depending on cell content

On 18 Oct, 13:22, Pete_UK wrote:
I can't think of a way - SUMIF relies on matching data from the range
in its first parameter to the contents of its second parameter, so if
you had blanks in column A it would not be able to match with it.

Is there a problem recording the receipt number on every row?

Pete

On Oct 18, 1:04 pm, Mik wrote:





Thanks for your responce Pete,


Is there a way of applying this without having to insert the receipt
number for all transactions?


Mike- Hide quoted text -


- Show quoted text -



No problem really,
It's just that the spreadsheet has so much content that requires an
input, that we are now reviewing all areas where the input could be
either automated or simplified.

So the less input required the better.

Thanks again,

Mike



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Sum data, depending on cell content

If you had a simple formula like:

=A2

in cell A3 and copied this down, then whenever you had a new receipt
number (let's say in A10) and entered 1234, then all the cells below
it would show this. If another new receipt number had to be entered in
A15, then the cells below that would show the new receipt no. So, you
would only have to enter the receipt number for the first entry, over-
writing the formula - saves a bit of inputting ...

Hope this helps.

Pete

On Oct 18, 1:28 pm, Mik wrote:
On 18 Oct, 13:22, Pete_UK wrote:





I can't think of a way - SUMIF relies on matching data from the range
in its first parameter to the contents of its second parameter, so if
you had blanks in column A it would not be able to match with it.


Is there a problem recording the receipt number on every row?


Pete


On Oct 18, 1:04 pm, Mik wrote:


Thanks for your responce Pete,


Is there a way of applying this without having to insert the receipt
number for all transactions?


Mike- Hide quoted text -


- Show quoted text -


No problem really,
It's just that the spreadsheet has so much content that requires an
input, that we are now reviewing all areas where the input could be
either automated or simplified.

So the less input required the better.

Thanks again,

Mike- Hide quoted text -

- Show quoted text -



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mik Mik is offline
external usenet poster
 
Posts: 42
Default Sum data, depending on cell content

On 18 Oct, 13:39, Pete_UK wrote:
If you had a simple formula like:

=A2

in cell A3 and copied this down, then whenever you had a new receipt
number (let's say in A10) and entered 1234, then all the cells below
it would show this. If another new receipt number had to be entered in
A15, then the cells below that would show the new receipt no. So, you
would only have to enter the receipt number for the first entry, over-
writing the formula - saves a bit of inputting ...

Hope this helps.

Pete

On Oct 18, 1:28 pm, Mik wrote:



On 18 Oct, 13:22, Pete_UK wrote:


I can't think of a way - SUMIF relies on matching data from the range
in its first parameter to the contents of its second parameter, so if
you had blanks in column A it would not be able to match with it.


Is there a problem recording the receipt number on every row?


Pete


On Oct 18, 1:04 pm, Mik wrote:


Thanks for your responce Pete,


Is there a way of applying this without having to insert the receipt
number for all transactions?


Mike- Hide quoted text -


- Show quoted text -


No problem really,
It's just that the spreadsheet has so much content that requires an
input, that we are now reviewing all areas where the input could be
either automated or simplified.


So the less input required the better.


Thanks again,


Mike- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -



Pete,

Thanks for your help.

Mike

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
copy comment content to cell content as data not as comment Lilach Excel Discussion (Misc queries) 2 June 21st 07 12:28 PM
how do i change cell background colour depending on its content? demble Excel Discussion (Misc queries) 2 May 2nd 07 05:50 PM
Copy content of cell to another depending on value of third cell(between worksheets) Zeljko Milak Excel Worksheet Functions 2 July 14th 06 07:17 PM
changing the colour of cells depending on the content. johnny.exe Excel Discussion (Misc queries) 3 January 26th 06 10:41 AM
Changing named range reference depending on a cell's content cparaske Excel Discussion (Misc queries) 1 July 29th 05 07:06 PM


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

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

About Us

"It's about Microsoft Excel"