Excel Excel 2007

Hi Everybody, I have a host of data to work with, and I am trying to make a complex selection. If you look at the example below:

A B C

1 1 1 0

2 1 2 0

3 2 1 1

4 2 1 1Basically I want to create a formula that select cells in Column A which =1, which in turn selects cells in B that have 1, then finally select cells in C that have 0 which would assign the sum of all cells in C that have 0 (only one here though).

Thanks so much for your help!

Your question is somewhat confusing. re:

"a formula that select cells"A formula can not

selectcells. The termselecthas a very specific meaning in Excel and only the user or VBA canselecta cell.re:

"assign the sum of all cells in C that have 0"What does "assign the sum" mean? Assign it to what?

re:

"the sum of all cells in C that have 0"The sum of all cells in C that have 0 is ... wait for it ... 0!

P.S. Follow the tip found in the following line the next time you need to post a data table.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Sorry, I am not too good when it comes to Excel as I am not too familar with the lingo.

If I want to count the number of '0's there are in Cell C, there would be two. However, I only want to count the number of '0's that meet a specific criteria, that being Cell A must be 1, and cell B must be 1 - If that pathway is followed, then the formula in the end will only count one '0' in the C column, in the first row.I hope thats clearer?

A B C 1 1 1 0 2 1 2 0 3 2 1 1 4 2 2 1

If you have Excel 2007, you can use the =COUNTIFS() function: =COUNTIFS(A1:A4,"=1",B1:B4,"=1",C1:C4,"=0")

MIKE

If you don't have Excel 2007 here is an ARRAY formula: =SUM((A1:A4=1)*(B1:B4=1)*(C1:C4=0))

To enter a formula as an array formula:

First type the formula in the cell

DO NOTpress EnterYou

MUSTpress the CTRL SHIFT and ENTER keys at the same time.You must do this the first time you enter the formula and whenever you edit the formula later.

If you have done this properly, Excel will display the formula enclosed in

curly braces { } and it will look like:{=SUM((A1:A4=1)*(B1:B4=1)*(C1:C4=0))}

You

do not type in the braces-- Excel will display them automatically.If you fail to enter the formula with CTRL SHIFT ENTER, the formula may return a #VALUE error or return an incorrect result.

MIKE

With 2003, you don't need an array formula. SUMPRODUCT will do it also.

=SUMPRODUCT((A1:A4=1)*(B1:B4=1)*(C1:C4=0))

This will also work in 2007/2010

P.S.

re: Wherever you used "Cell C, Cell B, CellA" in your latest response, it should be Column C. Column B and Column A.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Wonderful! Thanks guys that indeed did the trick.

Ask Your Question

Weekly Poll