Multiple Countif Statements

Question

I'm trying to create a countif formula on a range of data. I have 4 ranges that I would like to look at. If the age of a certain order is <30 days, 30-59 days, 60-89 days, or greater than 90 days. I would like to create a countif statement that adds the number of orders that fall in each of the ranges and return that count.

Here is what I have right now.

=COUNTIF('Open Sales Orders'!AH2:AH484,"<"&30)

So you want one formula to do multiple counting and produce multiple results? Don't think that's really possible by one formula alone. — Pankaj Jaju
But, perhaps you can try Pivot Table ... can you share some sample data? — Pankaj Jaju

barry houdini barry houdini · Accepted Answer · 2014-01-16T00:03:37

Your COUNTIF will work for <30, although I'd write it slightly differently

=COUNTIF('Open Sales Orders'!AH2:AH484,"<30")

and you can do similar for the largest values

=COUNTIF('Open Sales Orders'!AH2:AH484,">=90")

for the between conditions use COUNTIFS (with an "S") which allows multiple conditions, e.g.

=COUNTIFS('Open Sales Orders'!AH2:AH484,">=30",'Open Sales Orders'!AH2:AH484,"<60")

and

=COUNTIFS('Open Sales Orders'!AH2:AH484,">=60",'Open Sales Orders'!AH2:AH484,"<90")

Multiple Countif Statements

1 Answers