+ Post New Thread
Results 1 to 12 of 12
Office Software Thread, Excel Nested IF formula in Technical; Hello All, Imagine the spreadsheet: ... A B 1. 0 J 2. 1 K 3. 1 J 4. 0 L ...
  1. #1

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Excel Nested IF formula

    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

  2. #2

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    Code:
    COUNTIFS(A:A,1,B:B,"J")
    will do it, assuming you want the whole column. If you want specific ranges, do
    Code:
    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:
    Code:
    =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
    Code:
    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:
    Code:
    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
    Code:
    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
    Last edited by MattMitchell; 8th May 2009 at 02:32 PM.

  3. Thanks to MattMitchell from:

    park_bench (8th May 2009)

  4. #3

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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

  5. #4

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    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.

  6. #5
    The_Original_Invisible's Avatar
    Join Date
    May 2009
    Location
    Swindon
    Posts
    6
    Thank Post
    0
    Thanked 3 Times in 3 Posts
    Rep Power
    0
    =SUMPRODUCT(--($B$1:$B$5="J")*($A$1:$A$5=1))

    Hope it helps

    Lee

  7. Thanks to The_Original_Invisible from:

    park_bench (8th May 2009)

  8. #6

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    *slaps forehead*
    Nice one! Plus it works in 2003 which is better than COUNTIFS

  9. #7

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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

  10. #8

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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).

  11. #9
    The_Original_Invisible's Avatar
    Join Date
    May 2009
    Location
    Swindon
    Posts
    6
    Thank Post
    0
    Thanked 3 Times in 3 Posts
    Rep Power
    0
    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

  12. #10
    The_Original_Invisible's Avatar
    Join Date
    May 2009
    Location
    Swindon
    Posts
    6
    Thank Post
    0
    Thanked 3 Times in 3 Posts
    Rep Power
    0
    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

  13. Thanks to The_Original_Invisible from:

    park_bench (8th May 2009)

  14. #11

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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

  15. #12
    The_Original_Invisible's Avatar
    Join Date
    May 2009
    Location
    Swindon
    Posts
    6
    Thank Post
    0
    Thanked 3 Times in 3 Posts
    Rep Power
    0
    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

  16. Thanks to The_Original_Invisible from:

    park_bench (11th May 2009)



SHARE:
+ Post New Thread

Similar Threads

  1. Excel Formula Copy Problem
    By sqdge in forum Office Software
    Replies: 26
    Last Post: 10th February 2013, 07:18 AM
  2. Excel Formula
    By denon101 in forum How do you do....it?
    Replies: 3
    Last Post: 1st December 2008, 04:34 PM
  3. excel formula help again please
    By RabbieBurns in forum Windows
    Replies: 2
    Last Post: 13th August 2008, 06:31 PM
  4. Excel Question / Formula Needed
    By timbo343 in forum How do you do....it?
    Replies: 22
    Last Post: 4th August 2008, 10:06 PM
  5. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 05:56 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •