Importing cell values into formulas & SUMIF/PRODUCT formulas with 2 conditions

These questions expand on a previous thread of mine, in which I received excellent help: http://www.excelforum.com/showthread.php?t=639775

To shortly summarize that thread, I now have a formula that adds together the values in column B for rows that are labeled "X" in column A, so that for example the setup...

A B
X 1
Y 1
Z 1
X 2

...would return 3 if the condition is defined as "X". The formula looks like this:

=SUMPRODUCT(--(OFFSET($A$2;0;0;COUNTA($A:$A);1)="X")*OFFSET(B$2;0;0;COUNTA($A:$A);1))

(After implementing the above solution I've been told the simpler formula =SUMIF(A:A,"X",B:B) does the same thing, though I have yet to try it.)

Questions:

(1) Is it possible to make the condition "X" be drawn from a cell value - say D2 - so that when I change the value in D2 from "X" to "Y", this change is reflected in all formulas that refer to D2?

(2) How do I add another condition to either formula above so that it only adds the values in column B for all rows that are labeled "X" in column A and "Z" in column C?

Answer 1. Yes, just replace "X" with your cell (e.g. A1).

Answer 2. Add a term to your SUMPRODUCT for it to look at Column C for "Z" like so;

Please Login or Register  to view this content.

You can replace "Z" with another cell location also.

Work for you?

ChemistB

Works perfectly, thank you!

I must have put quotation marks around A1 when I tried simply referencing the cell.

Glad to hear you got it working. Thanks for the feedback.

ChemistB

After some further testing I've run into a problem.

If I use the autofilter function with two conditions in the formula it will mess up the calculation, e.g. if I sort by 'Ascending' it will yield different result than if I sort by 'Descending'. Autofilter works fine with just one condition. Is there something I can do with the formula to allow use of the autofilter function?

Bump, no response.