Double SUMIF

[J&SPriest]

I have a spreadsheet which has multiple columns. I have SUMIF'ed some of the columns but now I wish to sum depending upon criteria in two separate columns, e.g.

I have the following data
Letter Direction Amount
A In 1
A In 1
A Out 2
B Out 2
B In 1
B In 3
A Out 4
C In 2
C In 1

I have SUMIF'ed for A, B and C and also SUMIF'ed for In and Out but now I want to SUM the Amount for Letter and Direction, i.e. I want the SUM of Amount for A and In, A and Out, B and In, B and Out, etc.

How do i say basically say If Letter=A and Direction =In then sum the Amount?

Thanks
John

[DonkeyOte]

Pre XL2007 you are looking at SUMPRODUCT, ie:

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Out"),C1:C100)

Note with SUMPRODUCT (pre XL2007) you can not use entire column range references (A:A) nor should you - keep ranges to a min. so as to minimise impact on performance.

You might want to think about using a Pivot Table.

For more info. on both SUMPRODUCT (incl. use of double unary (--)) and Pivot Tables see appropriate links in my sig.

[J&SPriest]

Excellent. It does exactly what i want - now if only I understood why

Thanks
John

[DonkeyOte]

Have a read through of Bob Phillips' white paper on the SUMPRODUCT function - link is in my sig.

[rwgrietveld]

Pivot tables will get you the best results.

If you have just one request Letter=A Direction=Out then Advanced Filter would be an alternative for SUMPRODUCT