SUM Function - I am stuck...

[BELFRED6]

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?

 

[jeffreybrown]

Try...replace the ??? with a client ID

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

 

[Peter_SSs]

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)

 

[Gettingbetter]

or

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

Cheers
GB

 

[BELFRED6]

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.

 

[Gettingbetter]

See my reply?

 

[Peter_SSs]

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
	A	B	C	D	E
1	Client ID	Amt		Lower ID	200
2	150	1		Upper ID	299
3	201	2		Sum	15
4	180	3
5	999	4
6	275	5
7	402	6
8	181	7
9	200	8
10
SUMIF

 

[Jacophile]

{=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.

 

[BELFRED6]

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...

 

[Peter_SSs]

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