A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old November 18th 04, 03:42 PM
ScottBerger
external usenet poster
 
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

Ads
  #2  
Old November 18th 04, 07:09 PM
Frank Kabel
external usenet poster
 
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
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 01:19 PM.


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