# SUM Function - I am stuck...

#### BELFRED6

##### Board Regular
I have a table with client ID in column A (range A2:A200) and qty ordered by those clients listed in column B (range B2:B200). Clients’ ID numbers range from 101 to 999.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Except through filtering, how to return the sum of quantities ordered for stores with ID number between 100 and 199 ONLY?

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try...replace the ??? with a client ID

=Sumproduct(--(A2:A200=???),B2:B200)

I have a table with client ID in column A (range A2:A200) and qty ordered by those clients listed in column B (range B2:B200). Clients’ ID numbers range from 101 to 999.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Except through filtering, how to return the sum of quantities ordered for stores with ID number between 100 and 199 ONLY?
Like this?
=SUMIF(A2:A200,"<200",B2:B200)

or

=SUMIF(A2:A200,">100",B2:B200)-SUMIF(A2:A200,">999",B2:B200)

Cheers
GB

Sorry if I was not very clear in my request...

What I need is to get a filtered SUM of quantities ordered by stores with ID BETWEEN one ID number (eg 101) AND another ID number (eg 199). In other words, I need to put a low and high limit to my sum operation.
If I had only one limit, a SUM with IF function would do the job. Here I have two limits that need to be taken into account simultaneously.

Sorry if I was not very clear in my request...

What I need is to get a filtered SUM of quantities ordered by stores with ID BETWEEN one ID number (eg 101) AND another ID number (eg 199). In other words, I need to put a low and high limit to my sum operation.
If I had only one limit, a SUM with IF function would do the job. Here I have two limits that need to be taken into account simultaneously.
Similar to Gettingbetter's suggestion then:

Excel Workbook
ABCDE
1Client IDAmtLower ID200
21501Upper ID299
32012Sum15
41803
59994
62755
74026
81817
92008
10
SUMIF

{=SUM(IF((CustNum>=LL)*(CustNum<=UL),OrderQTY))}

Where LL and UL are references to cells containing Lower and Upper Limit of Customer number; CustNum and OrderQTY are A2:A200 and B2:B200 in your example.

It is an array formula so you need to enter it with control-shift-enter instead of just enter. That's how you get the {}'s.

Thank you.
What Peter_SSs and GettingBetter suggest work. Thank you. But sorry I don't understand the logics.

If I look for a range, say above 100 AND under 199, why do I get in the formula > 100 and > 199? Shouldn't it be >100 and <199 to limit my range? I know it is wrong since I tried and Excel return 0 values systematically. But ti sounds more logical to me.

So thank you for your help, I will use your formula. But to be honest, I don't understand it...

Thank you.
What Peter_SSs and GettingBetter suggest work. Thank you. But sorry I don't understand the logics.

If I look for a range, say above 100 AND under 199, why do I get in the formula > 100 and > 199? Shouldn't it be >100 and <199 to limit my range? I know it is wrong since I tried and Excel return 0 values systematically. But ti sounds more logical to me.

So thank you for your help, I will use your formula. But to be honest, I don't understand it...
Take a simple example. Say we want to sum the numbers >= 2 and <=4 in this sample:
1, 2, 3, 4, 5, 6

First sum all the numbers >= 2
2+3+4+5+6 = 20

Now sum the numbers > 4 (these are the numbers from the dirst sum that we don't want in our final answwer)
5+6 = 11

Now subtract (to get rid of the sum we didn't want from our first sum)
20-11 = 9

This is the result we wanted, as manually we would have done
2+3+4 = 9

Hope that makes sense

Replies
5
Views
202
Replies
9
Views
266
Replies
8
Views
435
Replies
13
Views
1K
Replies
1
Views
489

1,203,094
Messages
6,053,503
Members
444,667
Latest member
KWR21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back