+ Post New Thread
Results 1 to 15 of 15
Office Software Thread, Excel expert required - Help please in Technical; I'm clearly asking google the wrong question and can't get my head round this: I have 2 columns of data, ...
  1. #1

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,066
    Thank Post
    1,792
    Thanked 2,184 Times in 1,617 Posts
    Rep Power
    773

    Excel expert required - Help please

    I'm clearly asking google the wrong question and can't get my head round this:

    I have 2 columns of data, one contains integers between 0 and 4 and the other contains X or blank, something like this:
    A B
    0 x
    1 x
    2
    4
    0 x
    3
    1 x
    0

    What I want to do is count how many X there are in column B if the value in the column A is 1

    (So here IF column A =0, there are 2 X in Column B and so the answer is 2)

    Can it be done using excel functions, without hidden columns and without writing a macro?

  2. #2

    tech_guy's Avatar
    Join Date
    May 2007
    Location
    That little bit in the middle of Little Old England
    Posts
    8,135
    Thank Post
    1,908
    Thanked 1,344 Times in 742 Posts
    Blog Entries
    3
    Rep Power
    395
    Use a nested COUNT function in an IF function?

    Or COUNTIF?

    LINK
    Last edited by tech_guy; 13th May 2010 at 10:54 PM. Reason: I'm drunk & can't really think staright....

  3. #3

    Join Date
    May 2010
    Location
    Melbourne
    Posts
    1
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    If you only have x's in the B column, I'd suggest using the LEN() function.

    So you'd have something like the following formula: "=IF(A1=1,LEN(B1),"")"

  4. #4

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,759
    Thank Post
    3,269
    Thanked 1,052 Times in 973 Posts
    Rep Power
    365
    Sum If Function

    Excel: SumIf Function

    ?????

  5. #5
    donutcat's Avatar
    Join Date
    Dec 2006
    Location
    Bedford
    Posts
    115
    Thank Post
    7
    Thanked 12 Times in 11 Posts
    Rep Power
    22
    COUNTIF won't work as it works with only one set criteria, SUMIF won't count, it sums, so you will have to do the calculation in two parts. Create a new column on the right that will do the double IF statement, i.e. if a is the number you are looking for and b has the letter you are looking for then assign a value of 1, else leave the column empty. Then just sum the column.

    I have attached and example with the formulas in. If you wanted to add this function to lots of different sheets it may be worth writing a macro, but the formulas are not too arduous.

    Any probs PM me.
    Attached Files Attached Files

  6. #6

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,066
    Thank Post
    1,792
    Thanked 2,184 Times in 1,617 Posts
    Rep Power
    773
    I've been messing withusing COUNTA in column B, to create an integer value of 1 if there's anything in the cell...

    What I can't get right is the nested expression to get the right answer. I'm doing brilliantly at getting the sum of column A + the sum of column B

  7. #7

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,066
    Thank Post
    1,792
    Thanked 2,184 Times in 1,617 Posts
    Rep Power
    773
    Quote Originally Posted by donutcat View Post
    COUNTIF won't work as it works with only one set criteria, SUMIF won't count, it sums, so you will have to do the calculation in two parts. Create a new column on the right that will do the double IF statement, i.e. if a is the number you are looking for and b has the letter you are looking for then assign a value of 1, else leave the column empty. Then just sum the column.

    I have attached and example with the formulas in. If you wanted to add this function to lots of different sheets it may be worth writing a macro, but the formulas are not too arduous.

    Any probs PM me.

    Thanks... I had something like that, but you've got what I was aiming for.

    I was trying to avoid using a third column (which I'd have to hide) if at all possible... and do it all in one formula, if possible.

  8. #8
    pauljonze's Avatar
    Join Date
    May 2008
    Location
    Staffs
    Posts
    86
    Thank Post
    17
    Thanked 22 Times in 15 Posts
    Rep Power
    22
    Couldn't you just use a hidden column to equate to one when a=0 and b=x then summate it?

  9. #9

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,066
    Thank Post
    1,792
    Thanked 2,184 Times in 1,617 Posts
    Rep Power
    773
    Quote Originally Posted by pauljonze View Post
    Couldn't you just use a hidden column to equate to one when a=0 and b=x then summate it?
    Yeah, I was just trying to avoid a hidden column if I could! TBH, by now it's for no better reason than it must be posible, I just can't work out how!

  10. #10
    pauljonze's Avatar
    Join Date
    May 2008
    Location
    Staffs
    Posts
    86
    Thank Post
    17
    Thanked 22 Times in 15 Posts
    Rep Power
    22
    Oops! Sorry - didn't read previous post! Looks like a macro then?

  11. #11

    SYSMAN_MK's Avatar
    Join Date
    Sep 2005
    Posts
    4,005
    Thank Post
    489
    Thanked 1,340 Times in 728 Posts
    Rep Power
    428
    Code:
    =COUNT(IF((B1:B8="X")*(A1:A8=1),A1:A8))

    Change the arrays to suite your data. After copying the example to a blank worksheet, select the formula cell. Press F2, and then press CTRL+SHIFT+ENTER rather then just ENTER.

    See attached example file.

    My example is for the value 1 but works fine with 0.
    Attached Files Attached Files
    Last edited by SYSMAN_MK; 14th May 2010 at 09:32 AM.

  12. Thanks to SYSMAN_MK from:

    elsiegee40 (14th May 2010)

  13. #12

    Join Date
    Apr 2007
    Location
    Birmingham
    Posts
    142
    Thank Post
    6
    Thanked 45 Times in 39 Posts
    Rep Power
    22
    Could also use Sumproduct:

    =SUMPRODUCT((A1:A8=0)*(B1:B8="x"))

  14. Thanks to andy_b from:

    elsiegee40 (14th May 2010)

  15. #13

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,066
    Thank Post
    1,792
    Thanked 2,184 Times in 1,617 Posts
    Rep Power
    773
    Thanks both... that's what I was after. Looking at what I had, I was so near yet so far to Bossman's solution... sometimes you just need someone else to point out the obvious!

  16. #14

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    247
    Thank Post
    23
    Thanked 37 Times in 28 Posts
    Rep Power
    20
    If you're just after the answers, a pivot table's the quickest way to do it: use the abcd column as row headings, and a count of the x column for the data area. Takes about 15 seconds!

  17. Thanks to MattMitchell from:

    elsiegee40 (15th May 2010)

  18. #15

    Join Date
    Jun 2007
    Location
    Sheppey, Kent
    Posts
    9
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi,

    If your data is in A1 - B8, then put the following formula in C1 and drag down to C8:
    {=SUM(($A$1:$A$8=A1)*($B$1:$B$8="x"))}

    this is an array formula - use Ctrl Shift Enter to put the curly brackets in

    Regards


    Gary

SHARE:
+ Post New Thread

Similar Threads

  1. Photoshop Expert Needed
    By SC-UK in forum General Chat
    Replies: 2
    Last Post: 7th December 2010, 12:07 PM
  2. Replies: 1
    Last Post: 22nd December 2009, 05:05 PM
  3. need your expert opinion
    By lionsl2005 in forum AV and Multimedia Related
    Replies: 11
    Last Post: 14th December 2009, 02:53 PM
  4. Joomla Template Expert Needed
    By russdev in forum Web Development
    Replies: 3
    Last Post: 23rd April 2007, 03:29 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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