Use a nested COUNT function in an IF function?
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?
Last edited by tech_guy; 13th May 2010 at 10:54 PM. Reason: I'm drunk & can't really think staright....
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),"")"
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.
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
Couldn't you just use a hidden column to equate to one when a=0 and b=x then summate it?
Oops! Sorry - didn't read previous post! Looks like a macro then?
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 09:32 AM.
Could also use Sumproduct:
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!
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!
elsiegee40 (15th May 2010)
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
There are currently 1 users browsing this thread. (0 members and 1 guests)