I have a column called as COST as shown below, i need a excel formula to pick
the data count from cost column to my count column based on Cost Range column.
example:
Cost column
3
3.5
4
2
2.5
1

Cost Range column count column
4-3
2-1

Answer expected:

Cost Range column count column
4-3 3
2-1 3

Count column is nothing but it should count less than 4 and greater than 3
(3,3.5 and 4 are the answers)

Hi,
for option between 4-3

=COUNTIFS(A7:A12,"2",A7:A12,"<=4")

for option 1-2

=COUNTIFS(A7:A12,"=1",A7:A12,"<=2")

"Ranjith Kurian" wrote:

I have a column called as COST as shown below, i need a excel formula to pick
the data count from cost column to my count column based on Cost Range column.
example:
Cost column
3
3.5
4
2
2.5
1

Cost Range column count column
4-3
2-1

Answer expected:

Cost Range column count column
4-3 3
2-1 3

Count column is nothing but it should count less than 4 and greater than 3
(3,3.5 and 4 are the answers)

unfortunately we do't have countifs function in excel 2003

"Eduardo" wrote:

Hi,
for option between 4-3

=COUNTIFS(A7:A12,"2",A7:A12,"<=4")

for option 1-2

=COUNTIFS(A7:A12,"=1",A7:A12,"<=2")

"Ranjith Kurian" wrote:

I have a column called as COST as shown below, i need a excel formula to pick
the data count from cost column to my count column based on Cost Range column.
example:
Cost column
3
3.5
4
2
2.5
1

Cost Range column count column
4-3
2-1

Answer expected:

Cost Range column count column
4-3 3
2-1 3

Count column is nothing but it should count less than 4 and greater than 3
(3,3.5 and 4 are the answers)

Hi
I didn't know you were using 2003, try this

=COUNTIF(A7:A12,"2")-COUNTIF(A7:A12,"4")



"Ranjith Kurian" wrote:

unfortunately we do't have countifs function in excel 2003

"Eduardo" wrote:

Hi,
for option between 4-3

=COUNTIFS(A7:A12,"2",A7:A12,"<=4")

for option 1-2

=COUNTIFS(A7:A12,"=1",A7:A12,"<=2")

"Ranjith Kurian" wrote:

I have a column called as COST as shown below, i need a excel formula to pick
the data count from cost column to my count column based on Cost Range column.
example:
Cost column
3
3.5
4
2
2.5
1

Cost Range column count column
4-3
2-1

Answer expected:

Cost Range column count column
4-3 3
2-1 3

Count column is nothing but it should count less than 4 and greater than 3
(3,3.5 and 4 are the answers)

Hi,

Suppose data is in range C4:C9. Enter 4 in C13, 3 in D13, 2 in C14 and 1 in
D14. In cell E13, enter
=COUNTIF($C$4:$C$9,"="&D13)-COUNTIF($C$4:$C$9,""&C13) and copy down

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Ranjith Kurian" wrote in message
...
I have a column called as COST as shown below, i need a excel formula to
pick
the data count from cost column to my count column based on Cost Range
column.
example:
Cost column
3
3.5
4
2
2.5
1

Cost Range column count column
4-3
2-1

Answer expected:

Cost Range column count column
4-3 3
2-1 3

Count column is nothing but it should count less than 4 and greater than 3
(3,3.5 and 4 are the answers)

Hi,

One way to do this is:

=SUMPRODUCT(--(A1:A9=3),--(A1:A9<=4))



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ranjith Kurian" wrote:

I have a column called as COST as shown below, i need a excel formula to pick
the data count from cost column to my count column based on Cost Range column.
example:
Cost column
3
3.5
4
2
2.5
1

Cost Range column count column
4-3
2-1

Answer expected:

Cost Range column count column
4-3 3
2-1 3

Count column is nothing but it should count less than 4 and greater than 3
(3,3.5 and 4 are the answers)

=SUM(COUNTIF(A1:A6,{"=3","4"})*{1,1})


"Ranjith Kurian" wrote:

I have a column called as COST as shown below, i need a excel formula to pick
the data count from cost column to my count column based on Cost Range column.
example:
Cost column
3
3.5
4
2
2.5
1

Cost Range column count column
4-3
2-1

Answer expected:

Cost Range column count column
4-3 3
2-1 3

Count column is nothing but it should count less than 4 and greater than 3
(3,3.5 and 4 are the answers)

=SUM(COUNTIF(A1:A6,{"=3","4"})*{1,1})

You probably meant:

=SUM(COUNTIF(A1:A6,{"=3","4"})*{1,-1})

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=SUM(COUNTIF(A1:A6,{"=3","4"})*{1,1})


"Ranjith Kurian" wrote:

I have a column called as COST as shown below, i need a excel formula to
pick
the data count from cost column to my count column based on Cost Range
column.
example:
Cost column
3
3.5
4
2
2.5
1

Cost Range column count column
4-3
2-1

Answer expected:

Cost Range column count column
4-3 3
2-1 3

Count column is nothing but it should count less than 4 and greater than
3
(3,3.5 and 4 are the answers)