Remember Me?

#1
November 18th 04, 03:42 PM
 ScottBerger Posts: n/a
How to use SUMIF to return sums between two values located in cells

I have two rows of data where I would like to use SUMIF to look at the
first row data (Row A) , and sum the values in the second row (Row B)
if values are between those found in two cells (Rows C and D). I intend
to use the resulting bin values to compare different data sets, and
create a histogram like plots. There will be ~20,000 individual
output bins in the real data set, so doing it manually would be
problematic.

Example Data

Row A, B:

A1= 1 , B1= 3
A2= 1.5, B2=3
A3= 2, B3 =5
A4= 2.2, B4 = 10
A5= 3, B4= 2

Row C, D, E

C1=0, D1=1, E1= SUMIF A between C1 and less than D1 then Sum B (ANS:
0)
C2=1, D2=2, E2= SUMIF A between C2 and less than D2 then Sum B (ANS:
6)
C3=2, D3=3, E3= SUMIF A between C3 and less than D3 then Sum B (ANS:
15)
C4=3, D4=4, E4= SUMIF A between C4 and less than D4 then Sum B (ANS:
2)
C5=4, D5=5, E5= SUMIF A between C5 and less than D5 then Sum B (ANS:
0)
C6=5, D6=6, E6= SUMIF A between C6 and less than D6 then Sum B (ANS:
0)

--
ScottBerger
------------------------------------------------------------------------
ScottBerger's Profile: http://www.excelforum.com/member.php...o&userid=16553

#2
November 18th 04, 07:09 PM
 Frank Kabel Posts: n/a

Hi
try
=SUMIF(A:A,"=" &C1)-SUMIF(A:A,"" & D1)

"ScottBerger" wrote:

I have two rows of data where I would like to use SUMIF to look at the
first row data (Row A) , and sum the values in the second row (Row B)
if values are between those found in two cells (Rows C and D). I intend
to use the resulting bin values to compare different data sets, and
create a histogram like plots. There will be ~20,000 individual
output bins in the real data set, so doing it manually would be
problematic.

Example Data

Row A, B:

A1= 1 , B1= 3
A2= 1.5, B2=3
A3= 2, B3 =5
A4= 2.2, B4 = 10
A5= 3, B4= 2

Row C, D, E

C1=0, D1=1, E1= SUMIF A between C1 and less than D1 then Sum B (ANS:
0)
C2=1, D2=2, E2= SUMIF A between C2 and less than D2 then Sum B (ANS:
6)
C3=2, D3=3, E3= SUMIF A between C3 and less than D3 then Sum B (ANS:
15)
C4=3, D4=4, E4= SUMIF A between C4 and less than D4 then Sum B (ANS:
2)
C5=4, D5=5, E5= SUMIF A between C5 and less than D5 then Sum B (ANS:
0)
C6=5, D6=6, E6= SUMIF A between C6 and less than D6 then Sum B (ANS:
0)

--
ScottBerger
------------------------------------------------------------------------
ScottBerger's Profile: http://www.excelforum.com/member.php...o&userid=16553

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post kg Excel Discussion (Misc queries) 1 January 20th 05 12:53 AM Jamesy Excel Discussion (Misc queries) 3 January 10th 05 03:03 PM dranreb Excel Discussion (Misc queries) 4 December 9th 04 01:15 AM Dawnybros Excel Discussion (Misc queries) 2 December 2nd 04 04:35 PM Wendy Excel Worksheet Functions 3 November 3rd 04 04:32 PM

All times are GMT +1. The time now is 06:39 AM.