SUM cells with values within intervalls?

[tskogstrom]

Hi,
What is the best approach - or maybe you have a solution?

I need to check a column with values and sum the cells with values
within intervals, like 0-100000, 100001-500000 and 500001-1000000
There is a column with values within these intervals and as result I
want to have three rows with sums of these intervals.


I tried pivottables, but it seems not to be able to create intevals in
it

I have tried to create array formulas that sum the matrix values if
interval is true, but bite the dust. Can't find a formula that return
a matrix/areas with cells within the intervalls(to use with sum() )

I think of creating a help column with a IF formula checking interval
and if true, define the "interval name" - to be able to use in pivot
later on with its sum function

I think of creating a vba function returning above "inteval name" sum
directly

What is best solution - or a solution already made by you?

kind regards
tskogstrom

 

[Guest]

If your values are in column A try

=COUNTIF(A:A,">=100001")-COUNTIF(A:A,">500000")

to make things easier you can put your criteria in cells, e.g. C2= 100001
and D2 = 500000 and use

=COUNTIF(A:A,">="&C2)-COUNTIF(A:A,">"&D2)

 

[Don Guillett]

to count
=sumproduct((a2:a22>0)*(a2:a22<=100000))
to sum another column based on that criteria
=sumproduct((a2:a22>0)*(a2:a22<=100000)*b2:b22)