Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advanced SUMIF
I have 11 columns of data and would normally use sumif to add up the values
based on the doc type added, however now the VAT rate has changed I need to identify 2 critera in 2 columns and sumif wont allow that. Any help would be greatfully accepted |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advanced SUMIF
Best to help yourself by searching google
sumproduct:excel -- Don Guillett Microsoft MVP Excel SalesAid Software "Welshr2" wrote in message ... I have 11 columns of data and would normally use sumif to add up the values based on the doc type added, however now the VAT rate has changed I need to identify 2 critera in 2 columns and sumif wont allow that. Any help would be greatfully accepted |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advanced SUMIF
You can use SUMPRODUCT
=SUMPRODUCT(--(A1:A100="cond1"),--(B1:B100="cond2"),(C1:C100)) This will sum values in Col C for those rows where Col A has the value cond1 and Col B has the value cond2 "Welshr2" wrote: I have 11 columns of data and would normally use sumif to add up the values based on the doc type added, however now the VAT rate has changed I need to identify 2 critera in 2 columns and sumif wont allow that. Any help would be greatfully accepted |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advanced SUMIF
Check out this link...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Welshr2" wrote: I have 11 columns of data and would normally use sumif to add up the values based on the doc type added, however now the VAT rate has changed I need to identify 2 critera in 2 columns and sumif wont allow that. Any help would be greatfully accepted |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advanced SUMIF
On Jan 5, 4:06*pm, Welshr2 wrote:
I have 11 columns of data and would normally use sumif to add up the values based on the doc type added, however now the VAT rate has changed I need to identify 2 critera in 2 columns and sumif wont allow that. Any help would be greatfully accepted To elaborate a little more you can use Sumproduct like this =SUMPRODUCT((A1:A10=5)*(B1:B10=9)*1). This would count if both conditions were met in A and B. Here is how to add C if A and B are met. =SUMPRODUCT((A1:A10=5)*(B1:B10=9)*(C1:C10)). Always make sure that your arrays are the same size, like 1:10... Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Criteria for SUMIF | Excel Worksheet Functions | |||
Advanced Sumif formula | Excel Worksheet Functions | |||
Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...) | Excel Discussion (Misc queries) | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |