CountIf Array Formula Required for Two Range Data Set: Excel 2010

[Legacy 338537]

Hi guys,

Trying to develop an array formula that can handle two ranges and result in a simple count.

At present my data is split between two cells, one of which is a $ amount, the other is a date (as seen below).

Sale Price	Sale Date
$384,000	8/01/2013
$640,000	8/01/2013
$310,000	10/01/2013
$520,330	12/01/2014
$204,630	14/01/2014
$257,100	16/01/2014
$770,350	18/01/2014

<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

I am trying to group each $ amount in individual ranges (Say $500,000 - $599,999) and only calculate that range for each individual year.

As there is typically up to thousands of cells of data, doing this manually is incredibly time consuming and I am hoping to create a Array formula to help me out.

I have been able to aggregate the total $ amounts between each ranges with the following formula:

=COUNTIF($A$3:$A$300000,">="&450000)-COUNTIF($A$3:$A$300000,">="&499999)

(Please note this is for sales between $450,000 and $499,999 simply as an example and I will adjust the formula once complete to encompass new ranges)

However this does not break the data down into individual years which is what i'm stuck with. Any help would be greatly appreciated!

Many thanks in advance

 

[Aladin Akyurek]

Try to provide the output lay-out you desire for the sample you posted.

 

[Legacy 338537]

Thanks for your reply Aladin, apologies for the oversight! Please see the example below:

Year	$250,000 - $299,999	$300,000 - $349,999	$350,000 - $399,999	$400,000 - $449,999	$450,000 - $499,999	$500,000 - $549,999
2010
2011
2012
2013
2014
2015

<tbody>
</tbody>

As I said before, I'm happy to adjust the formula for each range as require, just looking to get a more solid starting point.

 

[sanrv1f]

Try this

Excel Workbook
	A	B
1	Sale Price	Sale Date
2	$384,000	08-01-2013
3	$640,000	08-01-2013
4	$310,000	10-01-2013
5	$520,330	12-01-2014
6	$204,630	14-01-2014
7	$257,100	16-01-2014
8	$770,350	18-01-2014
Sheet2

Excel 2010

Excel Workbook
	F	G	H	I	J	K	L
2		$250,000	$300,000	$350,000	$400,000	$450,000	$500,000
3	Year	$299,999	$349,999	$399,999	$449,999	$499,999	$549,999
4	2010	0	0	0	0	0	0
5	2011	0	0	0	0	0	0
6	2012	0	0	0	0	0	0
7	2013	0	1	1	0	0	0
8	2014	1	0	0	0	0	1
9	2015	0	0	0	0	0	0
Sheet2

Excel 2010

Cell Formulas
Range	Formula
G4	=SUMPRODUCT(--(YEAR($B$2:$B$8)=$F4),--($A$2:$A$8>=G$2),--($A$2:$A$8<=G$3))

 

[Aladin Akyurek]

Thanks for your reply Aladin, apologies for the oversight! Please see the example below:

Year	$250,000 - $299,999	$300,000 - $349,999	$350,000 - $399,999	$400,000 - $449,999	$450,000 - $499,999	$500,000 - $549,999
2010
2011
2012
2013
2014
2015

<tbody>
</tbody>

As I said before, I'm happy to adjust the formula for each range as require, just looking to get a more solid starting point.

What follows invokes a COUNTIFS formula which is a tad faster...

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Sale Price	Sale Date				$250,000 ​	$300,000 ​	$350,000 ​	$400,000 ​	$450,000 ​	$500,000 ​
2​	$384,000 ​	1/8/2013​			Year	$299,999 ​	$349,999 ​	$399,999 ​	$449,999 ​	$499,999 ​	$549,999 ​
3​	$640,000 ​	1/8/2013​			2010​	0​	0​	0​	0​	0​	0​
4​	$310,000 ​	1/10/2013​			2011​	0​	0​	0​	0​	0​	0​
5​	$520,330 ​	1/12/2014​			2012​	0​	0​	0​	0​	0​	0​
6​	$204,630 ​	1/14/2014​			2013​	0​	1​	1​	0​	0​	0​
7​	$257,100 ​	1/16/2014​			2014​	1​	0​	0​	0​	0​	1​
8​	$770,350 ​	1/18/2014​			2015​	0​	0​	0​	0​	0​	0​

<tbody>
</tbody>

F3, copy across and down:

=COUNTIFS($B:$B,">="&DATE($E3,1,1),$B:$B,"<="&DATE($E3,12,31),$A:$A,">="&F$1,$A:$A,"<="&F$2)

 

[Legacy 338537]

Thank you both for your swift replies! Both options worked perfectly. If I wanted to do a Median of the ranges rather than a Sum would I need to change the formula dramatically?

 

[Aladin Akyurek]

Thank you both for your swift replies! Both options worked perfectly. If I wanted to do a Median of the ranges rather than a Sum would I need to change the formula dramatically?

For conditional median...

F5, control+shift+enter, not just enter, copy across, and down:

=IFERROR(MEDIAN(IF(YEAR($B$2:$B$8)=$E3,IF($A$2:$A$8>=F$1,IF($A$2:$A$8<=F$2,$A$2:$A$8)))),"")

 

[Legacy 338537]

For conditional median...

F5, control+shift+enter, not just enter, copy across, and down:

=IFERROR(MEDIAN(IF(YEAR($B$2:$B$8)=$E3,IF($A$2:$A$8>=F$1,IF($A$2:$A$8<=F$2,$A$2:$A$8)))),"")

Fantastic Aladin, worked perfectly! Much appreciated!

 

[Aladin Akyurek]

Fantastic Aladin, worked perfectly! Much appreciated!

You are welcome.

 

[Legacy 338537]

You are welcome.

Sorry to annoy you Aladin but I just had one last question. I'm trying to do the same formula but for half yearly segmentation instead:

I have tried the following (which is my best attempt):

=MEDIAN(IF($B5:$B300000,">="&DATE($H41,1,1),$B5:$B300000),"<="&DATE($H41,6,30),IF($A$5:$A$300000>=AK$3,IF($A$5:$A$300000<=AK$4,$A$5:$A$300000)))

Where-
B5:B300000 is the range of Sale Dates.
A5:A300000 is the range of Sale Prices
H41 is the Year
and
AK3 and AK4 are the Two Price Ranges

However it returns a #VALUE! Error. Any suggestions?