Copy COUNTIFS formula while keeping range and changing criteria

[JamieErvin]

Hi Everyone,

I'm struggling with an excel issue that I'm sure there's a simply solution to. I've got a series of long lists of numbers (ex. 1.23423, 1.34535, 1.256588, etc...) that I'm hoping to condense into a shorter series numeric classes. I'm hoping for a final product where a column (column C below) contains the number of total values from the original list that fall into a certain range, or class. For example, in the table below, Class 1.1 would contain all values that fall between 1.1 and 1.2:

Row # Column A Column B Column C
Original List Classes Number in Class
1 1.123 1 0
2 1.243 1.1 1
3 1.256 1.2 1
4 1.333 1.3 3
5 1.345 1.4 2
6 1.367
7 1.432
8 1.456

I've found a COUNTIFS formula that will accomplish this. For example, if I wanted the values between 1.1 and 1.2, I would use:

=COUNTIFS($O$6:$O$959, ">=1.2", $O$6:$O$959, "<1.3")

The problem is, I need to do this hundreds of times, and so it's really inefficient to alter the formula each time manually. When I try to copy the formula downward, it restarts the criteria numbering and so my values are off. Does anybody know the best way to write the formula above so that I can copy it and have criteria ranges increase to reflect the values in the list? I'd like for the formula to copy in the following way:


=COUNTIFS($O$6:$O$959, ">=1.2", $O$6:$O$959, "<1.3")

=COUNTIFS($O$6:$O$959, ">=1.3", $O$6:$O$959, "<1.4")

=COUNTIFS($O$6:$O$959, ">=1.4", $O$6:$O$959, "<1.5")

Any clues? I've experimented with a bunch of things but nothing seems to work.

Thanks!
Jamie

 

[Jonmo1]

Ok, so you already have the lower bound of the range in a cell (Classes, 1.1 1.2 etc)

Try
=COUNTIFS($O$6:$O$959, ">="&P1, $O$6:$O$959, "<"&P1+0.1)

P1 is the cell containing the lower bound value 1.1

 

[JamieErvin]

Thank you!! That works perfectly.

 

[Jonmo1]

You're welcome

 

[aadeli]

Hey Bryan -

I'm having a similar issue, and wondering if this solution may also work. Essentially, I am trying to count the number of "first time customers" every month. So in my list of customers, I want to count the number of customers that have ordered a case in that month, but never in a previous month. This means that I have the following formulas:

June 2015 (appearing in column C): =COUNTIFS(C22:C1002, ">0",A22:A1002,"Customer")
July 2015 (appearing in column D): =COUNTIFS(D22:D1002, ">0", C22:C1002, "=0",A22:A1002,"Customer")
August 2015 (appearing in column E): =COUNTIFS(E22:E1002, ">0", D22:D1002, "=0", C22:C1002, "=0", A22:A1002,"Customer")

I have to manually add in the conditions for each month - I can drag the formula across, but it does not aggregate the columns, it only keeps the set size for a different time frame. Any thoughts??

Thank you!!

 

[FDibbins]

★ aadeli you need to start your own thread with your question - you can reference this thread if you think it is relevant

(also, Bryan is where Jonmo comes from, not his name )

 

[aadeli]

Whoops! Thanks for the help - I'm brand new to the site, so thanks for the tips!