I have several columns with numerical values; A, B, C, D, and E, with column
F being a total of the row.
At the bottom of each column is a "Yes", "No" validation. Column A, C, and
E have been validated to "Yes".
How do I get, say, cell G1 to see if the Validation for the Column is "Yes"
then subtract the row value in that column from the total in column F?

Columns A, B, C, D, E = Ordered from each sales person
Columns F = Total items ordered
Yes, No validation = Items delivered
Column G = total items YET to be delivered
(A column is completely delivered at one time)

Thanks!

Assuming you have "Yes" or "No" in row 9 and your valued in A1:E1 then
entering the following in G1 will give you the sum of cells for "Yes" columns
=SUMPRODUCT(--($A$9:$E$9="Yes"),(A1:E1))

If you want Total minus this sum then you can have
=SUMPRODUCT(--($A$9:$E$9="No"),(A1:E1))

or

=SUM(A1:E1) - SUMPRODUCT(--($A$9:$E$9="Yes"),(A1:E1))
--
If you find this post helpful pl. choose "Yes"...


"Todd" wrote:

I have several columns with numerical values; A, B, C, D, and E, with column
F being a total of the row.
At the bottom of each column is a "Yes", "No" validation. Column A, C, and
E have been validated to "Yes".
How do I get, say, cell G1 to see if the Validation for the Column is "Yes"
then subtract the row value in that column from the total in column F?

Columns A, B, C, D, E = Ordered from each sales person
Columns F = Total items ordered
Yes, No validation = Items delivered
Column G = total items YET to be delivered
(A column is completely delivered at one time)

Thanks!