Check for Values

cbedell · Apr 11th, 2002, 05:36 PM

Gotta row of values and want to summarize the number of values that fall within certain ranges (ie. <5; equal to 5 but less than 10; equal to 10 but less than 15; and 15 of greater. Would like to reflect the count for each range in a seperate cell. Can anyone help me out?

lars · Apr 11th, 2002, 06:02 PM

=SUM((E45:E56>=5)*(E45:E56<=8)*(F45:F56))

Try this and enter it as an array control/shift/Enter

Whatever your range is adjust and assuming you have values next to criteria
numbers in column E and values you want summed in column F

RichardS · Apr 11th, 2002, 06:06 PM

If I have the data in from A1 to AX1, then entyer the folowing formula in cells A2, B2, C2, and D2, I get the results you want
A2 =COUNTIF(A1:AX1,"<5")
B2 =COUNTIF(A1:AX1,"<10")-A2
C2 =COUNTIF(A1:AX1,"<15")-SUM(A2:B2)
D2 =COUNTIF(A1:AX1,">14")

Is this what you're after?
Richard

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 05:36 PM	#1
cbedell New Member Join Date: Apr 2002 Posts: 6	Gotta row of values and want to summarize the number of values that fall within certain ranges (ie. <5; equal to 5 but less than 10; equal to 10 but less than 15; and 15 of greater. Would like to reflect the count for each range in a seperate cell. Can anyone help me out?

Apr 11th, 2002, 06:02 PM	#2
lars Board Regular Join Date: Mar 2002 Location: California Posts: 105	=SUM((E45:E56>=5)(E45:E56<=8)(F45:F56)) Try this and enter it as an array control/shift/Enter Whatever your range is adjust and assuming you have values next to criteria numbers in column E and values you want summed in column F

Apr 11th, 2002, 06:06 PM	#3
RichardS Board Regular Join Date: Feb 2002 Location: Victoria, Australia Posts: 761	If I have the data in from A1 to AX1, then entyer the folowing formula in cells A2, B2, C2, and D2, I get the results you want A2 =COUNTIF(A1:AX1,"<5") B2 =COUNTIF(A1:AX1,"<10")-A2 C2 =COUNTIF(A1:AX1,"<15")-SUM(A2:B2) D2 =COUNTIF(A1:AX1,">14") Is this what you're after? Richard