Count number of values in the different ranges

[hsandeep]

a column contains 'amount'. The amount can be from 1-999999. Some cells also contains #N/A (thr' formula) & blank (thr' formula).

I want to get NUMBERS of 'amount' whose value is
1-10000
10001-20000
20001-30000
30001-40000
40001-50000
50001 and above.

 

[hsandeep]

I'll give an example:
480
10002
500009
999

So for sum for values in the range 10001 20000 answer should come:10002
For sum 1-10000: answer is 1479.

I can punch 10001, 20000, & so on in different cells if required

 

[Aladin Akyurek]

How do you obtain 1479 15000?

 

[Rick Rothstein]

I'll give an example:
480
10002
500009
999

So for sum for values in the range 10001 20000 answer should come:10002
For sum 1-10000: answer is 1479.

I can punch 10001, 20000, & so on in different cells if required

You can use two SUMIF function calls to get your totals. For example, between (and including) 10001 and 20000...

=SUMIF(A:A,"<=20000")-SUMIF(A:A,"<10001")

Note the first SUMIF use "less than or equal to" whereas the second SUMIF uses only "less than".

 

[hsandeep]

This works for SUM. Please also COUNT the number of such values. I may have #N/A also in the range.

 

[Aladin Akyurek]

This works for SUM. Please also COUNT the number of such values. I may have #N/A also in the range.

The same logic applies...

=COUNTIF(Range,">="&E2)-COUNTIF(Range,">"&F2)

where E2 = 1 and F2 10000, for example.