sumif
The basic SUMIF function in Excel is limited to one If criteria. So how do you calculate the value using multiple criteria?
Use this array formula – you can use named ranges, as in the example. However, the named range must refer to the same rows: i.e. you could use named ranges referring to A2:A1000 and B2:B1000, but A2:A500 and B2:B2000 would not work.
{=SUM(IF((CData=$A$9)*(BData=$B$9),EData))}
This calculates the sum of records in the range EData where the named range CData = $A$9 AND BData = $B$9.
You must press CTRL + SHIFT + ENTER for the array formula to function.
For best practice you should also add an IF(ISERROR) statement, setting a value if an error is obtained:
{=IF(ISERROR(SUM(IF((CData=$A$9)*(BData=$B$9),EData))),”0.00″,
SUM(IF((CData=$A$9)*(BData=$B$9),EData)))}
On an error, the cell value is returned as “0.00″ – you might want to change this to any value appropriate for the specific worksheet you’re using.
The above example functions as an AND statement. You can modify this to an OR by replacing * with +, calculating the sum of values in EData where CData = $A$9 OR BData = $B$9:
{=SUM(IF((CData=$A$9)+(BData=$B$9),EData))}
