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

L

Legacy 338537

Guest
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).

[TABLE="width: 148"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Sale Price[/TD]
[TD="align: center"]Sale Date[/TD]
[/TR]
[TR]
[TD="align: center"]$384,000[/TD]
[TD="align: center"]8/01/2013[/TD]
[/TR]
[TR]
[TD="align: center"]$640,000[/TD]
[TD="align: center"]8/01/2013[/TD]
[/TR]
[TR]
[TD="align: center"]$310,000[/TD]
[TD="align: center"]10/01/2013[/TD]
[/TR]
[TR]
[TD="align: center"]$520,330[/TD]
[TD="align: center"]12/01/2014[/TD]
[/TR]
[TR]
[TD="align: center"]$204,630[/TD]
[TD="align: center"]14/01/2014[/TD]
[/TR]
[TR]
[TD="align: center"]$257,100[/TD]
[TD="align: center"]16/01/2014[/TD]
[/TR]
[TR]
[TD="align: center"]$770,350[/TD]
[TD="align: center"]18/01/2014[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Thanks for your reply Aladin, apologies for the oversight! Please see the example below:

[TABLE="width: 448"]
<tbody>[TR]
[TD="align: center"]Year[/TD]
[TD="align: center"]$250,000 - $299,999[/TD]
[TD="align: center"]$300,000 - $349,999[/TD]
[TD="align: center"]$350,000 - $399,999[/TD]
[TD="align: center"]$400,000 - $449,999[/TD]
[TD="align: center"]$450,000 - $499,999[/TD]
[TD]$500,000 - $549,999[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
Try this
Excel Workbook
AB
1Sale PriceSale Date
2$384,00008-01-2013
3$640,00008-01-2013
4$310,00010-01-2013
5$520,33012-01-2014
6$204,63014-01-2014
7$257,10016-01-2014
8$770,35018-01-2014
Sheet2
Excel 2010
Excel Workbook
FGHIJKL
2$250,000$300,000$350,000$400,000$450,000$500,000
3Year$299,999$349,999$399,999$449,999$499,999$549,999
42010000000
52011000000
62012000000
72013011000
82014100001
92015000000
Sheet2
Excel 2010
Cell Formulas
RangeFormula
G4=SUMPRODUCT(--(YEAR($B$2:$B$8)=$F4),--($A$2:$A$8>=G$2),--($A$2:$A$8<=G$3))
 
Upvote 0
STOCK said:
Thanks for your reply Aladin, apologies for the oversight! Please see the example below:

[TABLE="width: 448"]
<tbody>[TR]
[TD="align: center"]Year[/TD]
[TD="align: center"]$250,000 - $299,999[/TD]
[TD="align: center"]$300,000 - $349,999[/TD]
[TD="align: center"]$350,000 - $399,999[/TD]
[TD="align: center"]$400,000 - $449,999[/TD]
[TD="align: center"]$450,000 - $499,999[/TD]
[TD]$500,000 - $549,999[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
Click to expand...

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

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


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)
 
Upvote 0
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?
 
Upvote 0
STOCK said:
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?
Click to expand...

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)))),"")
 
Upvote 0
Aladin Akyurek said:
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)))),"")
Click to expand...


Fantastic Aladin, worked perfectly! Much appreciated!
 
Upvote 0
Aladin Akyurek said:
You are welcome.
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Returning 1 or (blank) from separate columns
Replies
6
Views
735
nogstai
N
D
  • Question Question
How to write formula that searches for a populated previous date if the date specifed doesn't exist in a data set?
Replies
1
Views
853
dave2018
D
B
  • Question Question
Excel to ignore 2 blanks / Fuse Formula
Replies
0
Views
388
Beanybala
B

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top