Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
I have the following data set: Term Amount 0233 1,240 0233 3,655 0240 10 0240 151 0243 675 I'm trying set up a table to sum the total amount that meets the following conditions: 1. Term = 0233 2. for amounts between 1,000 and 2,000 What formula is best for this operation and can anyone help me out with how to set it up? Brigitte -- Brigitte ------------------------------------------------------------------------ Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782 View this thread: http://www.excelforum.com/showthread...hreadid=548604 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
One way ..
Assuming data within A2:B10, col A = Term, col B = Amount Placed in say, E1: =SUMPRODUCT(($A$2:$A$10="0233")*($B$2:$B$101000)* ($B$2:$B$10<2000),$B$2:$B$10) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brigitte" wrote: I have the following data set: Term Amount 0233 1,240 0233 3,655 0240 10 0240 151 0243 675 I'm trying set up a table to sum the total amount that meets the following conditions: 1. Term = 0233 2. for amounts between 1,000 and 2,000 What formula is best for this operation and can anyone help me out with how to set it up? Brigitte -- Brigitte ------------------------------------------------------------------------ Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782 View this thread: http://www.excelforum.com/showthread...hreadid=548604 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
I'm getting a #value. Do you know how to troubleshoot this type of output? -- Brigitte ------------------------------------------------------------------------ Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782 View this thread: http://www.excelforum.com/showthread...hreadid=548604 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
"Brigitte" wrote:
I'm getting a #value. Do you know how to troubleshoot this type of output? The ranges: $A$2:$A$10, $B$2:$B$10 need to be identical in size Re-check this in your adaptation of the formula to suit over there .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
Thanks. I can't believe I overlooked my ranges. I had them set for a single cell rather than the full column. One more thing.... I also need a count of the amounts that make up the total matching the same criteria. Ideas? -- Brigitte ------------------------------------------------------------------------ Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782 View this thread: http://www.excelforum.com/showthread...hreadid=548604 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
=SUMPRODUCT(($A$2:$A$10="0233")*($B$2:$B$101000)* ($B$2:$B$10<2000))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Brigitte" wrote in message ... Thanks. I can't believe I overlooked my ranges. I had them set for a single cell rather than the full column. One more thing.... I also need a count of the amounts that make up the total matching the same criteria. Ideas? -- Brigitte ------------------------------------------------------------------------ Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782 View this thread: http://www.excelforum.com/showthread...hreadid=548604 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
"Brigitte" wrote:
Thanks. I can't believe I overlooked my ranges. I had them set for a single cell rather than the full column. Glad you got it working ! One more thing.... I also need a count of the amounts that make up the total matching the same criteria. Ideas? This slight adaptation of the earlier formula returns the count: =SUMPRODUCT(($A$2:$A$10="0233")*($B$2:$B$101000)* ($B$2:$B$10<2000)) [the last term: .. ,$B$2:$B$10) in the earlier formula is removed] -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
PERFECT!! Thanks so much! -- Brigitte ------------------------------------------------------------------------ Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782 View this thread: http://www.excelforum.com/showthread...hreadid=548604 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
"Brigitte" wrote:
PERFECT!! Thanks so much! You're welcome ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
I thought you might be in the land of nod by now Max <g
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Max" wrote in message ... Thanks for the assist, Bob ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
"Bob Phillips" wrote:
I thought you might be in the land of nod by now Max <g Aha, but I was in serious training to stay awake ahead of the World Cup <bg All the best to England in the event ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
I am one of the few Englishmen who don't believe that England have a chance,
with or without Rooney. My bet is on Italia. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Max" wrote in message ... "Bob Phillips" wrote: I thought you might be in the land of nod by now Max <g Aha, but I was in serious training to stay awake ahead of the World Cup <bg All the best to England in the event ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple conditions sum
Bob,
All the best to Italy, then <bg ! (but think your heart is still on England ..) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote: I am one of the few Englishmen who don't believe that England have a chance, with or without Rooney. My bet is on Italia. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total or Count based on multiple conditions | Excel Discussion (Misc queries) | |||
Multiple conditions and multiple return values | Excel Worksheet Functions | |||
Vlookup with multiple conditions | Excel Worksheet Functions | |||
SUM based on multiple conditions - SORRY, URGENT!!! | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |