Use a nested COUNT function in an IF function?
Or COUNTIF?
LINK
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?
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....
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),"")"
Sum If Function
Excel: SumIf Function
?????
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?
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.
Last edited by SYSMAN_MK; 14th May 2010 at 09:32 AM.
elsiegee40 (14th May 2010)
Could also use Sumproduct:
=SUMPRODUCT((A1:A8=0)*(B1:B8="x"))
elsiegee40 (14th May 2010)
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)
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
There are currently 1 users browsing this thread. (0 members and 1 guests)