COUNTIFS function

hfler

Board Regular
Joined
Jun 10, 2008
Messages
95
Hello--

I have a lot of sales data, and I want to find out how many sales there are between 10K-50K, 50K-100K, etc. How do I do a COUNTIFS function for greater than/less than? For instance, this is how I think it should look, but this does not work: COUNTIFS(A:A,">10000","<50000").

If anyone has any advice, I would appreciate it. Thanks a lot!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I do not know how to use the COUNTIFS function, but I would use a sumproduct for this.

=SUMPRODUCT(--(A1:A10000<50000),--(A1:A10000>10000))

If you use Excel2007, then you can use EntireColumn references (A:A) in place of A1:A10000.
 

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,534
One possible solution - set up another column :-

B1=if(and(a1>10000,a1<50000),1,0)

and then sum the values in Column B.

HTH

Kaps
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=countifs(a:a,">10000",a:a,"<50000")
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
one way ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCD
19000GreaterThanLessThanOrEqualToCounnt
21300010000500006
317000500001000003
421000***
525000***
629000***
753000***
869000***
999000***
10120000***
1149000***
Sheet13_Jeanie


</body></html>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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
Top