Prev: Extract numbers from a string of text
Next: pick up particular number from a table
From: Confused in TX on 27 May 2010 17:32
I want to insert subtotals only for items containing at least two specified
costs.

A B C
1 Item Name Cost Title Cost Value
2 Item 1 Cost 1 5
3 Item 1 Cost 2 3
4 Item 1 Cost 3 4
5 Item 1 Cost 4 1
6 Item 2 Cost 1 9
7 Item 3 Cost 3 6

This setup continues for 28,000 rows. I want to separately sum all the cost
values for each item number which contains both a cost 1 and cost 2. Are
conditional subtotals possible, so that only items meeting the criteria would
be subtotaled?

I would also be interested in capturing the value of the corresponding
item's cost 1 in the cell adjacent to the item's subtotal - in this example,
the value 5 from cell C2 would be insertee in the cell next to the item's new
subtotal. What formula could be used for this separate function?

Thanks!
From: Confused in TX on 30 May 2010 18:09
Is there a better way to accomplish this? Instead of conditional; subtotals,
is it possible to extract all costs for every item which contains the two
specified costs and move them to a different tab in the spreadsheet? A pivot
table could then be used to provide item totals.

"Confused in TX" wrote:

> I want to insert subtotals only for items containing at least two specified
> costs.
>
> A B C
> 1 Item Name Cost Title Cost Value
> 2 Item 1 Cost 1 5
> 3 Item 1 Cost 2 3
> 4 Item 1 Cost 3 4
> 5 Item 1 Cost 4 1
> 6 Item 2 Cost 1 9
> 7 Item 3 Cost 3 6
>
> This setup continues for 28,000 rows. I want to separately sum all the cost
> values for each item number which contains both a cost 1 and cost 2. Are
> conditional subtotals possible, so that only items meeting the criteria would
> be subtotaled?
>
> I would also be interested in capturing the value of the corresponding
> item's cost 1 in the cell adjacent to the item's subtotal - in this example,
> the value 5 from cell C2 would be insertee in the cell next to the item's new
> subtotal. What formula could be used for this separate function?
>
> Thanks!
 | 
Pages: 1
Prev: Extract numbers from a string of text
Next: pick up particular number from a table