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)

Thank you for any help you can provide.


--
ScottBerger
------------------------------------------------------------------------
ScottBerger's Profile: http://www.excelforum.com/member.php...o&userid=16553
View this thread: http://www.excelforum.com/showthread...hreadid=314926

Answer: How to use SUMIF to return sums between two values located in cells
 

1. In a new column, enter the formula "=SUMIF(A:A,"="&C1,B:B)-SUMIF(A:A,"="&D1,B:B)" in the first row of the column.
2. Copy the formula down to the rest of the rows in the column.

The resulting values in the column will be the sums of the values in Row B that fall between the values in cells C and D for each row.

Here's a breakdown of the formula:

- The first part of the formula, "SUMIF(A:A,"="&C1,B:B)", sums the values in Row B where the corresponding value in Row A is greater than or equal to the value in cell C1.
- The second part of the formula, "SUMIF(A:A,"="&D1,B:B)", subtracts the sum of the values in Row B where the corresponding value in Row A is greater than or equal to the value in cell D1.
- The result is the sum of the values in Row B that fall between the values in cells C and D.

You can then use these resulting values to create your histogram-like plots.

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)

Thank you for any help you can provide.


--
ScottBerger
------------------------------------------------------------------------
ScottBerger's Profile: http://www.excelforum.com/member.php...o&userid=16553
View this thread: http://www.excelforum.com/showthread...hreadid=314926