Countif with an expression as criteria

[Mark_Porter]

I have a table with 2 columns. The first (column A)is numbered
sequentially from 1 to 52. The second (column B) has different whole
number values in it. I want to be able to count the values in column B
that corespond to the range 1 to x in column A. I can not get this to
work. Any ideas would be appreciated.

Mark

 

[Don Guillett]

how about
=sumproduct((a1:a1000<=40)*(b1:b1000))
or
count(b1:b40)

 

[duane]

i think this should work to get a count of the values:

=sumproduct((bY:bZ>0)*(aY:aZ>=1)*(aY:aZ<=x))

where Y and Z are the first and last rows of the range of dat

 

[duane]

giving credit where credit is due this is something I've picked up fro
the real experts on this board..........

 

[Arvi Laanemets]

Hi

=SUM(OFFSET(B2,,,x,1))
for case your numbers start from row2.

Arvi Laanemets

 

[Ron Rosenfeld]

I have a table with 2 columns. The first (column A)is numbered
sequentially from 1 to 52. The second (column B) has different whole
number values in it. I want to be able to count the values in column B
that corespond to the range 1 to x in column A. I can not get this to
work. Any ideas would be appreciated.

Mark

If I understand you correctly, you want to SUM the values in Column B that have
a number from 1 to x in the same row in Column A.

=SUMIF(A:A,"<=x",B:B)

or, if x is in cell G1

=SUMIF(A:A,"<="&G1,B:B)

============================

If, on the other hand, you want to SUM the values in column be that correspond
to the range x to y in Column A, where x > 1, then use this formula:

=SUMIF(A:A,">=x",B:B) - SUMIF(A:A,">y",B:B)

or, with cell references

=SUMIF(A:A,">="&G1,B:B) - SUMIF(A:A,">"&G2,B:B)


--ron

 

[Arvi Laanemets]

Hi

If I understand you correctly, you want to SUM the values in Column B that have
a number from 1 to x in the same row in Column A.

=SUMIF(A:A,"<=x",B:B)

or, if x is in cell G1

=SUMIF(A:A,"<="&G1,B:B)

When sums are wanted in for every row, and numbers in column A are ordered,
then this will do:
=SUM(B$2:B2)
into some cell in row 2 (p.e. C2)
and copy down.


Arvi Laanemets

 

[Ron Rosenfeld]

When sums are wanted in for every row, and numbers in column A are ordered,
then this will do:
=SUM(B$2:B2)
into some cell in row 2 (p.e. C2)
and copy down.

I may have misunderstood the question. Perhaps he just wants a running total,
in which case, as you point out, the answer is a fairly simple function.

--ron