Using SUM with Data Validation.

Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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