Trouble with COUNTIFS Formula

edited 12/09/19 in Formulas and Functions


I was wondering if someone could please help me with a countifs formula that contains multiple ranges and criteria. 

Using the table below, I need to be able to figure out how many fresh food deliveries were done in a month. I have different locations I pick up from, and deliver to. I have a column that has what people ordered (fruits & vegetables). Lastly I have a column that asks for the order date & delivery date. 

On the first day of every month, I need to be able to work out how many deliveries I made for the previous month. I need to be able to include the date range for the previous month, (but exclude any deliveries that may have occurred after the month in question). 

To make things more complicated, I only want to count the fruits that were delivered in that month,.*

*I don't want to separate my fruits & vegetables into different columns, I need them to be in the same column. 

If its possible, I would also like to try and find out what fruits were delivered and ordered in the same month, as well as how many of each fruit was also ordered each month. 

Any help with this would be greatly appreciated! :) 

Screen Shot 2019-08-15 at 9.25.04 pm.png



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The first step would be to specify what is considered a fruit. There are many different ways to do his.


    After that it is a basic COUNTIFS formula.


    =COUNTIFS(criteria_range_1, criteria_1, criteria_range_2, criteria_2, criteria_range_3, criteria_3, .............................................................)


    If you are able to provide a grid or series of grids that shows the breakdown(s) you are looking to achieve along with a way of identifying fruit vs vegetable, we would be able to give you a much more specific solution.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!