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, ...
13th May 2010, 11:25 PM #1
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:
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?
IDG Tech News
13th May 2010, 11:48 PM #2
Use a nested COUNT function in an IF function?
Last edited by tech_guy; 13th May 2010 at 11:54 PM.
Reason: I'm drunk & can't really think staright....
14th May 2010, 06:28 AM #3
- Rep Power
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),"")"
14th May 2010, 08:11 AM #4
Sum If Function
Excel: SumIf Function
14th May 2010, 08:58 AM #5
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.
14th May 2010, 09:25 AM #6
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
14th May 2010, 09:27 AM #7
Originally Posted by donutcat
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.
14th May 2010, 09:28 AM #8
Couldn't you just use a hidden column to equate to one when a=0 and b=x then summate it?
14th May 2010, 09:34 AM #9
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!
Originally Posted by pauljonze
14th May 2010, 09:39 AM #10
Oops! Sorry - didn't read previous post! Looks like a macro then?
14th May 2010, 10:14 AM #11
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.
Last edited by SYSMAN_MK; 14th May 2010 at 10:32 AM.
Thanks to SYSMAN_MK from:
elsiegee40 (14th May 2010)
14th May 2010, 10:29 AM #12
Could also use Sumproduct:
Thanks to andy_b from:
elsiegee40 (14th May 2010)
14th May 2010, 10:35 AM #13
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!
15th May 2010, 09:10 AM #14
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!
Thanks to MattMitchell from:
elsiegee40 (15th May 2010)
17th May 2010, 09:23 AM #15
- Rep Power
If your data is in A1 - B8, then put the following formula in C1 and drag down to C8:
this is an array formula - use Ctrl Shift Enter to put the curly brackets in
By SC-UK in forum General Chat
Last Post: 7th December 2010, 01:07 PM
By DaveP in forum Downloads
Last Post: 22nd December 2009, 06:05 PM
By lionsl2005 in forum AV and Multimedia Related
Last Post: 14th December 2009, 03:53 PM
By russdev in forum Web Development
Last Post: 23rd April 2007, 04:29 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)