Selecting cell value for a sum, based on a condition

From: Robert Christie (anonymous_at_discussions.microsoft.com)
Date: 04/21/04 

Date: Tue, 20 Apr 2004 21:37:40 -0700

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?
>
>
>.
>

Relevant Pages