Using SUM with Data Validation.

Closed
Average Joe - Jan 4, 2011 at 09:03 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 7, 2011 at 09:52 AM
Hello, I am ok with excel but I cannot figure this out. I am trying to make a list of different items and their cost as I go through the list I want to have a data validation (Drop Downs) determine if the cost should be summed or not so for example in column D row 3 starts the data validation (yes and no, the data is linked to Column S row 1 and 2 respectively). In column E I will put the price of the item. In F3 I want the total of the Yes validated items to appear. For example:
_D_____E______F___
Sold | Value | Total
_________________
Yes | \$5.00 | \$6.25
No | \$22.00 |
Yes | \$1.25 |
No | \$6.00 |
No | \$4.00 |

Is this even possible. Basically ignore the value to the right if the validated data is No. Do I need to change something to make this happen? Thanks in advance.

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jan 7, 2011 at 09:52 AM
Hi Average Joe,

You are looking for an array formula.
Take a look at the following formula:
=SUM(IF(A1:A5="Yes",1,0)*(B1:B5))
Confirm this formula by hitting Ctrl+Shift+Enter.
If done correctly curly brackets {} will enclose the formula.

Best regards,
Trowa