Multiple Cell Selections Based on Eachother

Excel Excel 2007
November 13, 2010 at 01:33:40
Specs: Windows 64, i7820/8G
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 1

Basically 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!


See More: Multiple Cell Selections Based on Eachother

Report •


#1
November 13, 2010 at 05:11:56
Your question is somewhat confusing.

re: "a formula that select cells"

A formula can not select cells. The term select has a very specific meaning in Excel and only the user or VBA can select a 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 this How-To.


Report •

#2
November 13, 2010 at 10:46:23
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


Report •

#3
November 14, 2010 at 07:40:47
If you have Excel 2007, you can use the =COUNTIFS() function:

=COUNTIFS(A1:A4,"=1",B1:B4,"=1",C1:C4,"=0")

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 14, 2010 at 07:50:40
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 NOT press Enter

You MUST press 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

http://www.skeptic.com/


Report •

#5
November 14, 2010 at 09:45:06
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, Cell A" in your latest response, it should be Column C. Column B and Column A.

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


Report •

#6
November 14, 2010 at 22:58:45
Wonderful! Thanks guys that indeed did the trick.

Report •

Ask Question