Selecting cell value for a sum, based on a condition

[Andy]

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?

 

[Peo Sjoblom]

I fail to see the logic?

 

[Andy]

The values reflect either a unit cost for an item going into a building or a cost
based on the square footage. Not all items are costed based on per unit, but
costed based on how many square feet of construction. Hope that clarifies why I
need to sum the values this way.

 

[Robert Christie]

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.

 

[Andy]

Hi Bob,

Thanks for replying. What you recommended works well when the sheet 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 sheet 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.

 

[Frank Kabel]

Hi andy
and how do you know which item is unit based and which is not?

 

[Andy]

Hi Frank,

An example is probably best. Let's say we want to calculate the cost of the
inside doors on a three storey building. One possible cost for these doors is
based on each door, while another may be based on the square footage of the wall
space (S.F. Wall). These values are gathered from various publications that list
construction templates for various buildings and structures. The cost type
selected in the spreadsheet (thus the cost that will be used in the sum) will be
determined by the nature of the product used or how it's quoted. In another
area, there will be a form that states the square footage of the floors, walls,
etc. These figures will then be used with the S.F. values (Square Footage) to
help calculate the estimate, while the other items will simply use the quantity
entered for each item required (Unit Cost).

 

[Frank Kabel]

Hi
still not sure but based on your example try
=SUMPRODUCT(--(LEFT(A1:A100,4)="S.F."),C1:C100)+SUMPRODUCT
(--(LEFT(A1:A100,4)<>"S.F."),B1:B100)
Though I'd assume you have to multiply the first summand
with a square foot factor

 

[Robert Christie]

Hi Andy

When giving data examples it helps other users to
understand what it is you are trying to acheive.
Examples of the answer you expect increases the correct
help is given.
At times it's hard to put your question into just words.
But a question together with an example and expected
result greatly helps.
In your example if "Each" is selected what cells should
be Added together and what answer are you expecting?

Regards
Bob C.

 

[Andy]

Thanks Frank. I'll need to play with this a bit to get a handle on it, but it
appears to be something that will do the job. Thanks again for taking the time
to have a look.

 

[Andy]

Good point. Let me try this again. Let's say there are 4 items that need to be
costed for the estimate. Two items are per unit (Each) and two are per square
foot of floor space (S.F. Floor). The table below reflects both sets of costs
for these items.

A B C D
E
1 Each/S.F Unit Cost S.F. Cost
2 Each 5 .45
3 Each 9 .55
4 S.F. Floor 4 .30
5 S.F. Floor 6 .40
6 SubTotal
14.70
7
8 There would be additional rows and subtotals depending on the number of items
selected in another form.
9

For the above table, the total cost of these items would be 14.70 (5 + 9 + .30 +
..40)