I need to divide by a cell only if another cell contains text.

Copper Contributor

I’m trying to divide by a cell that will move from day to day.


Basically, C2 contains a total that I want to divide by. The cell I want to divide by is in sheet “2” but that cell will move based on the day (it will remain in column K). To find the right cell in column K to divide by is dependent on the content of column A (i.e. John Smith).


I’m not sure what formula would be best for this.

2 Replies



In order for anybody to help you, you're going to have to be both more clear and more complete.


For example, you mention that cell C2 contains a number that you want to "divide by," but you also describe a cell that is roaming around in column K of sheet 2 as the cell you want to "divide by."  I'm assuming you really mean that cell C2 is the number to be divided, and it is to be divided by  that number roaming around in column K of sheet 2.


Then there's the matter of John Smith who might be locked up in a cell of his own, but occasionally let out to be replaced by John Doe (?) in which case the row in column K will change (?)


You see the problem? 


More clarity, please; more thoroughness, please. Ideally, post a copy of this workbook on OneDrive or GoogleDrive and paste a link here that grants access. But only if you can also supply a better description of what's going on.



@whydoe1336 You can use SUMIFS to calculate the cumulative total for "John Smith" and or other names if you also need assumed averages for various names - repeat the SUMIFS in additional cells for the other names you are tracking. If you just want the cumulative total for this tax year (UK), then 2 ranges and criteria need to be added, both picking the column with the date via Z:Z for example and the criteria ">31/03/2023", and in the second condition, "<01/04/2024".

You then need to divide the SUMIFS, by COUNTIFS to get the number of occurrences there is data for the name in the date range. This is the best generic solution which may help your unclear description.