[ms office - 2003] Excel Nested IF formula

[park_bench]

Hello All,

 

Imagine the spreadsheet:

 

... A B

1. 0 J

2. 1 K

3. 1 J

4. 0 L

5. 1 L

 

I want a formula that states:

 

Count all the cells in column B that contain "J" IF column A = 1. So the result of the formula for the above sheet would be 1 (cell B3 only).

 

I'm trying to knock together a formula with COUNTIF and the AND operator without much success.

 

Can someone give me a pointer please?

 

Thanks in advance,

 

Ben

[MattMitchell]

COUNTIFS(A:A,1,B:B,"J")

will do it, assuming you want the whole column. If you want specific ranges, do

COUNTIFS(A1:A254,1,B1:B254,"J")

Unfortunately, COUNTIFS() is new to Excel 2007, but there other ways round this...

 

1) Add an extra column (say column C) and put this formula in:

=IF(A1=1, IF(B1="J", 1, 0), 0)

and sum it to get your answer.

 

or

2) Make a pivot table, depending on what you're wanting from the information.

 

or

3) put the damned data in Access instead - it's better at doing things like this - and then you can get your answer from a query like

SELECT Count(*) AS the_answer FROM mydata WHERE col_A=1 AND col_B='J';

 

4) If you want something a bit more interesting, like the same count for each different letter, in Access you could do something like this:

SELECT col_B as the_letter, Count(*) AS positives
FROM mydata
WHERE col_A=1
GROUP BY col_B;

 

4) or you could do a pivot table in Access too! Not as silly as it sounds, since it's easier to filter and manipulate the pivot table with an Access query anyway.

For counting all the zeroes and giving overall totals

TRANSFORM Count(*)
SELECT col_B AS the_letter, Count(*) as all_results
FROM mydata
GROUP BY col_B
PIVOT col_A;

which will give you a table with each row having the letter, the total number of records, the count for 0 and the count for 1

 

 

Hope this helps,

Matt

Edited May 8, 2009 by MattMitchell

[park_bench]

Thanks Matt,

 

For the very comprehensive answer. I wanted to avoid adding more columns and the COUNTIFS is very elegant.

 

Maybe not worth the upgrade fee on its own though!

 

Thanks again,

 

Ben

[MattMitchell]

You could add a column and then hide it - you can still reference it from a visible part of the worksheet! That might be the easiest way. And 2007 *everything* is all a bit weird IMHO - looks pretty but the interface has moved pretty much everything you're used to finding to a new place.

[The_Original_Invisible]

=SUMPRODUCT(--($B$1:$B$5="J")*($A$1:$A$5=1))

 

Hope it helps

 

Lee

[MattMitchell]

*slaps forehead*

Nice one! Plus it works in 2003 which is better than COUNTIFS

[park_bench]

Thanks that works!

 

It's worth noting if you are trying to implement this formula that the arrays being multiplied must be of the same size or it returns an error.

 

=SUMPRODUCT(--($B$1:$B$5="J")*($A$1:$A$5=1))

 

What does the '--' operator do by the way? It seems to return the same result without it.

 

Thanks again,

 

Ben

[park_bench]

As an aside...

 

If I wanted to add another criteria to the formula I could use:

 

=SUMPRODUCT(--($B$1:$B$5="J")*($A$1:$A$5=1))+SUMPRODUCT(--($B$1:$B$5="K")*($A$1:$A$5=1))

 

But is there a more elegant way of doing this?

 

(Sorry for the random question, I'm trying to be an chippy little autodidact).

[The_Original_Invisible]

In this instance it doesn't make any difference, but in simple terms it counts the number of rows where the criteria is met. If you wanted to sum values you would leave the -- out. You could put in *1 instead of -- if you wanted to. The double unary is the neatest to me though! :-)

 

If your data was layed out thus:-

 

0 J 10

1 K 20

1 J 30

0 L 50

1 L 60

 

And you wanted to know the sum of column C then you would use

 

=SUMPRODUCT(($C$1:$C$5)*($B$1:$B$5="J")*($A$1:$A$5=1))

 

Which says give me the sum of $C$1:$C$5 when $B$1:$B$5="J" and $A$1:$A$5=1 ( and you'd get the value 30 returned)

 

By the way, if you wanted to be REALLY clever, you could also put in multiple criteria such as

 

=SUMPRODUCT(($C$1:$C$5)*($B$1:$B$5={"J","K"})*($A$1:$A$5=1))

 

Which says give me the sum of $C$1:$C$5 when $B$1:$B$5="J" or $B$1:$B$5="K" and $A$1:$A$5=1 ( and you'd get the value 50 returned)

 

Hope that makes sense?

 

If not, there's another more detailed explanation of it here

 

McGimpsey & Associates : Excel : Formulae : Why "--"

 

Lee

[The_Original_Invisible]

I expect you've worked it our from my previous mail, but...

 

 

=SUMPRODUCT(--($B$1:$B$5={"J","K"})*($A$1:$A$5=1))

 

Lee

[park_bench]

Wow!

 

You've been doing this a while then! Thanks very much for taking the time to not only help but explain the concepts as well.

 

Cheers!

 

Ben

[The_Original_Invisible]

5 years or so, yeah. But I'm self taught and have just picked things up as I've gone along

 

I tend to use specific Excel forums (or used to use when I was less experienced) for Excel queries. Now I make sure I keep all my formulas in one place to refer to at a later date. No point re-inventing the wheel!

 

A good newish one is the Excel User Group

 

The people on it are local and very friendly and helpful. I attended a conference of theirs last month and they're putting another one on later this year I believe?

 

Lee