Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 18th 04, 03:42 PM
ScottBerger
 
Posts: n/a
Default 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


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

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I lookup and return different values when the lookup value. kg Excel Discussion (Misc queries) 1 January 20th 05 12:53 AM
LOOKUP FUNCTION WITH SUMS VALUES Jamesy Excel Discussion (Misc queries) 3 January 10th 05 03:03 PM
delete values in several cells without deleting the formulas dranreb Excel Discussion (Misc queries) 4 December 9th 04 01:15 AM
How to add a button to restore all altered cells original values? Dawnybros Excel Discussion (Misc queries) 2 December 2nd 04 04:35 PM
How to look up and return multiple values Wendy Excel Worksheet Functions 3 November 3rd 04 04:32 PM


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

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017