If/then referencing of two different cells?

Copper Contributor

Hello! Not sure if this will make sense, but I'm wondering if there is a way to have one cell show the value of only one of two reference cells (the one that has a value other than 0). 


To further explain, I'm trying to set up a sheet that will determine costs either by bulk or by person for an event. I would like to have the option of either using the bulk cost of supplies or cost per person. If per person, it would have to calculate a total via formula and I would set up one cell that does that, and have one cell that I could enter just the bulk value.  My hope is to have a different cell pull either the bulk total or the value calculated by the formula. Is this possible?

1 Reply


Assuming that one of the two cells will always be blank or 0, you can use


=MAX(Per_Person, Bulk)


For example, if per person cost is in C2 and bulk cost in E2:


=MAX(C2, E2)