SUMIF with multiple arguements

[G]

I am trying to use a sumif function with more than 2
arguements but have been unsuccessful to date.

=SUMIF(range,criteria,sum_range)

I want to evaluate 2 columns in the range based on an AND
() function as the criteria.

Is this possible? If not what is the alternative?

 

[xlbo]

You can only have 1 criteria in a SUMIF
Thee are 3 possibilities for you:

1: Pivot table

2: Array formula

3: SUMPRODUCT

For 1, see the help files
For 2:
=SUM((A1:A100=1)*(B1:B100="x")*(C1:C100))
enter this using CTRL+SHIFT+ENTER. If you do it right { } will appear around the formula
What this does is sum upthe values inC where A = 1 AND B = x

For 3:
=SUMPRODUCT((A1:A100=1)*(B1:B100="x")*(C1:C100))
very similar to the array formula but doesn't need to be entered with CTRL+SHIFT+ENTER - just a normal ENTER

 

[Arvi Laanemets]

Hi

Use SUMPRODUCT()

=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2)*(SumRange))
Range1 and Range2 can be same or different. All ranges must have same
dimension