• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If>Then formula


New Member

I am trying to create a formula that uses (dynamic) data in a cell that has a drop down menu. However, if I leave the cell blank I want it to use the cumulative data from everything in the drop down combined.

For example: I have multiple sales people and I have a drop down menu that shows all 29 of them. The formula will essentially give me the opportunity to see sales pitches and closed sales by individual (after choosing them from the drop down), but if I don't choose a specific salesperson from the drop down I would like to receive the cumulative pitches and closes from all 29.

I would assume a formula that states if A2"" then sum (selected cells) but if A2"Salesperson 1" then count only the corresponding pitches and closes.

I would imagine a countif formula.

Please let me know if I am on the right track, and also the command to count all. I saw somewhere that it is "*" but that didn't seem to work in a different and simpler formula that I tried.

You could add a user called "All Salesmen" and place him at teh top of your list

then write your formula to do the summation based on the user

=+If(A2="All Salesmen", sum all, sum the individual salesman)