SUMIF Function

[Setts]

How do I use SUMIF or another function to add a column of figures based on
criteria in two adjacent columns? The criteria can be either true or false.
For example, in one instance I might want to add figures based on a match in
a Column A and B and in another instance based on a match in Column A but not
B. I am using Excel 2003.

 

[Dave]

How does the formula know what criteria you want in each case?
Dave.

 

[Setts]

In each case for the criteria I select two adjacent cells, one containing a
stock symbol, e.g. MSFT for Microsoft, and the other an Option symbol. When
the criteria match, in this case MSFT and blank, I want to add up a figure in
a third column. In other cases I want to add up when the stock symbol
matches and the adjacent column is NOT blank. I am familiar with the SUMIF
function but want the first two arguments to identify two columns.

 

[Dave]

Hi,
One way is with SUMPRODUCT.

With your stock symbol data in C2:C100
With your option symbol data in D2100
With your figures to add in E2:E100

Type the stock symbol you want as criteria into F2
Type the option symbol you want as criteria into G2

=SUMPRODUCT(--(C2:C100=F2)*(D2100=G2)*(E2:E100))

Regards - Dave.