How to use Countif function similar to the Count function in Subtotals 1

[wec43wec]

Is there a way to use the countif function like you would use the “count” function in the “subtotal” option in excel?

I have a list of data (over 10 columns and over 2000 rows) that I need to test a condition in “one column” and count those cells that meet that condition within each primary group which as an example I would use “state” as the primary group and “Days on Market” as the test column. My criteria for Days on the Market would change from state to state and that criteria is listed on another sheet.

A written explanation of the formula I would like to use is as follows:

>> At each change in “STATE” count the number of rows that have data in column titled “DAYS ON MARKET” less than a number on data sheet ABC in cell A1. notetest field may be a different number for each state as noted in the example below)


An example of test data would be as follows:

“State” Sales Price “Days on Market”
FLA 100000 100
FLA 200000 20
FLA 100000 20
FLA 200000 20
FLA 100000 100
FLA 200000 100
FLA 100000 100
FLA 200000 100
CA 100000 100
CA 200000 50
CA 100000 50
CA 200000 50
CA 100000 50
CA 200000 100
CA 100000 100
CA 200000 100




Results should be

“FLA” “3” (if test field is set as “ below 30)
“CA” “4” (if test field is set as “below 60)

Note: I would ask that any solution not be VBA coding

Thanks !

 

[macropod]

Hi wec43wec,

You could use an array formula like:
=SUM(IF((C1:C17="FLA")*(E1:E17<30),1))
You can, of course, replace the "FLA" and 30 with references to cells containing the relevant data.

Cheers

[MS MVP - Word]

 

[SkipVought]

Hi,

Have you checked out the Data > Subtotal... feature?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wec43wec]

Skip - Data>Subtotal will not work for me becuase it does not have a "countif" function that will allow me to test a condition(s).

Macropod's response may have provided a solution that may work. I will practice and/or apply his suggestion to see how well it does.

Thanks to both of you !

 

[macropod]

Hi wec43wec,

Do note that the formula I posted probably has the columns offset from what you want (I developed/tested in a ws with other data in cols A & B). The data you posted are better suited to:
=SUM(IF((A1:A17="FLA")*(C1:C17<30),1))

Cheers


[MS MVP - Word]

 

[wec43wec]

Macropod - your solution worked GREEEEAAAAATTT !

Thanks - I sure would like to know how that array worked / and how the "1" in the formula worded.

This saves a lot of time - thanks again.

WEC43WEC

 

[macropod]

Hi wec43wec,

Basically, the formula says:
"IF A1:A17="FLA" AND C1:C17<30 THEN populate an array with 1, otherwise 0. Then, having populated the array, sum the 1s.

Cheers


[MS MVP - Word]

 

[wec43wec]

Macropod - you deserve more then 2 stars Thanks !