Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with restrictions
I'm trying to create a formula that says "if column a=81500 AND if column
b=1885, then do a sumif(columnC,"<529999","539999",columnD). So, basically, I want to do a sumif function but put restrictions on it and only add up those numbers in column D that meet the criteria. To make things more difficult, I'd like the sumif funtion to lookup a range of numbers (ie: 530000 thru 539999) vs. a single number in column C and return the total of everything that matches that range from column D. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with restrictions
Try this:
=SUMPRODUCT((A1:A1000=81500)*(B1:B1000=1885)*(C1:C 1000529999)*(C1:C1000<539999)*(D1:D1000) Adjust range references to suit your situation (SUMPRODUCT won't work on whole columns) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "GfaCS" wrote in message ... I'm trying to create a formula that says "if column a=81500 AND if column b=1885, then do a sumif(columnC,"<529999","539999",columnD). So, basically, I want to do a sumif function but put restrictions on it and only add up those numbers in column D that meet the criteria. To make things more difficult, I'd like the sumif funtion to lookup a range of numbers (ie: 530000 thru 539999) vs. a single number in column C and return the total of everything that matches that range from column D. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with restrictions
Read this requirement a little different from Ron:
.. sumif(columnC,"<529999","539999",columnD) Think you could try something like this: =SUMPRODUCT((A1:A10=81500)*(B1:B10=1885)*((C1:C10< 529999)+(C1:C10539999))*D1:D10) Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "GfaCS" wrote: I'm trying to create a formula that says "if column a=81500 AND if column b=1885, then do a sumif(columnC,"<529999","539999",columnD). So, basically, I want to do a sumif function but put restrictions on it and only add up those numbers in column D that meet the criteria. To make things more difficult, I'd like the sumif funtion to lookup a range of numbers (ie: 530000 thru 539999) vs. a single number in column C and return the total of everything that matches that range from column D. Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with restrictions
This is perfect! Thank you SO much!!!
"Ron Coderre" wrote: Try this: =SUMPRODUCT((A1:A1000=81500)*(B1:B1000=1885)*(C1:C 1000529999)*(C1:C1000<539999)*(D1:D1000) Adjust range references to suit your situation (SUMPRODUCT won't work on whole columns) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "GfaCS" wrote in message ... I'm trying to create a formula that says "if column a=81500 AND if column b=1885, then do a sumif(columnC,"<529999","539999",columnD). So, basically, I want to do a sumif function but put restrictions on it and only add up those numbers in column D that meet the criteria. To make things more difficult, I'd like the sumif funtion to lookup a range of numbers (ie: 530000 thru 539999) vs. a single number in column C and return the total of everything that matches that range from column D. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with restrictions
I'm glad I could help....Thanks for letting me know.
Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "GfaCS" wrote in message ... This is perfect! Thank you SO much!!! "Ron Coderre" wrote: Try this: =SUMPRODUCT((A1:A1000=81500)*(B1:B1000=1885)*(C1:C 1000529999)*(C1:C1000<539999)*(D1:D1000) Adjust range references to suit your situation (SUMPRODUCT won't work on whole columns) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "GfaCS" wrote in message ... I'm trying to create a formula that says "if column a=81500 AND if column b=1885, then do a sumif(columnC,"<529999","539999",columnD). So, basically, I want to do a sumif function but put restrictions on it and only add up those numbers in column D that meet the criteria. To make things more difficult, I'd like the sumif funtion to lookup a range of numbers (ie: 530000 thru 539999) vs. a single number in column C and return the total of everything that matches that range from column D. Any suggestions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with restrictions
.. then do a sumif(columnC,"<529999","539999",columnD)
was wondering what you meant by your pseudo formula above? it was a bit misleading to put it that way -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with restrictions
Hey Max,
First off, thank you for responding. I really do appreciate your time and input. Secondly, my overall intention with this formula was to look at each row and if the criteria matched, to add up the dollar amount in that row. For example, Column "A" is a business unit, Column "B" is a sub code, and Column "C" is a project code. The dollar amounts reside in Column "D". My first intent was to say, if my criteria matches columns "A", "B", AND "C", then sum up the dollar amounts in "D". On top of that, I also wanted to be able to search "C" for a range of project codes (ie: all project codes between 529999 and 539999) instead of just one code. That's what I tried to convey with the below formula, but obviously wasn't super successful. I'll try to be extra clear with any future requests. Again, thank you! "Max" wrote: .. then do a sumif(columnC,"<529999","539999",columnD) was wondering what you meant by your pseudo formula above? it was a bit misleading to put it that way -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with restrictions
First, thanks for posting back
Think I understood fairly well all of the multi-conditions that you originally expressed <g The only one which proved confusing was this your original post as expressed: .. sumif(columnC,"<529999","539999",columnD) hints strongly at the "opposite" of your clarification: .. (ie: all project codes between 529999 and 539999) If ever you really need it as: .. sumif(columnC,"<529999","539999",columnD) ie considering: all project codes less than 529999, and all project codes greater than 539999 (the 2 "outer" bounds) then my earlier suggestion would do just that -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "GfaCS" wrote in message ... Hey Max, First off, thank you for responding. I really do appreciate your time and input. Secondly, my overall intention with this formula was to look at each row and if the criteria matched, to add up the dollar amount in that row. For example, Column "A" is a business unit, Column "B" is a sub code, and Column "C" is a project code. The dollar amounts reside in Column "D". My first intent was to say, if my criteria matches columns "A", "B", AND "C", then sum up the dollar amounts in "D". On top of that, I also wanted to be able to search "C" for a range of project codes (ie: all project codes between 529999 and 539999) instead of just one code. That's what I tried to convey with the below formula, but obviously wasn't super successful. I'll try to be extra clear with any future requests. Again, thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel help and AD GPO restrictions | Excel Discussion (Misc queries) | |||
Text Restrictions | Excel Worksheet Functions | |||
How to Add Restrictions to ComboBox...??? | Excel Worksheet Functions | |||
How to add Restrictions to Combo Box?/ | Excel Worksheet Functions | |||
How to do look up with restrictions | Excel Discussion (Misc queries) |