Re: Selecting cell value for a sum, based on a condition

From: Andy (spam_at_goaway.now)
Date: 04/21/04 

Date: Wed, 21 Apr 2004 05:21:49 GMT

Hi Bob,

Thanks for replying. What you recommended works well when the *** can have
hidden rows. In this case, all the values are from a table (paper form, not
digital) that has both types of costs in it. The person using this *** does
not have to enter the value of the costs (Unit or Square Foot), simply select
which type it is from column A. This selection will determine which cost is used
for the sum. The number of rows will vary as that information is gathered by
choosing individual row items on another form. This is all for my father-in-law
who does similar things by hand (or very basic sheets) and I'm working on
automating as much of this estimation procedure as I can. I'm surprised what
I've learned and accomplished over the last few days, but I have a long way to
go.

"Robert Christie" <anonymous@discussions.microsoft.com> wrote in message
news:1e6701c4275a$60f64a30$a601280a@phx.gbl...
> Hi Andy
> In your example in B8 enter =Subtotal(9,B2:B6) and in C8
> enter =Subtotal(9,C2:C6)
> In D8 enter =B8+C8
> Select row 1, goto Data--Filter--Autofilter and click on
> column A down arrow.
> Select from dropdown list Each.
> Only the 'Each" rows will show and the Subtotal sum's
> only the filtered values.
> Rows 7, 8, 9, etc will appear as normal.
> Row 8 will have your totals.
>
> HTH
> Bob C.
>
>
> >-----Original Message-----
> >Trying to come up with a formula or method that will
> enable me to sum values
> >based on a condition. For example, I have three columns
> which contain a
> >condition and two amounts. If the condition is of
> the 'each' variety, one value
> >will be used in the sum. If the condition is of
> the "square foot" variety,
> >another value will be used. Here is a small diagram
> that may help visualize
> >this:
> >
> > A B
> C D
> >1 Measure Unit Cost S.F. Cost
> Summed Total
> >2 Each 3.00 .30
> >3 S.F. Floor 4.00 .40
> >5 S.F. Wall 5.00 .50
> >6 Each 6.00 .60
> >
> >In the above example, The amounts at B2, C3, C4 and B5
> would be summed. Any
> >ideas?
> >
> >
> >.
> >