Excel expert required - Help please

• 13th May 2010, 10:25 PM
elsiegee40
Excel expert required - Help please

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?
• 13th May 2010, 10:48 PM
tech_guy
Use a nested COUNT function in an IF function?

Or COUNTIF?

• 14th May 2010, 05:28 AM
Henri
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, 07:11 AM
mac_shinobi
Sum If Function

Excel: SumIf Function

?????
• 14th May 2010, 07:58 AM
donutcat
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, 08:25 AM
elsiegee40
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 :confused:
• 14th May 2010, 08:27 AM
elsiegee40
Quote:

Originally Posted by donutcat
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.
• 14th May 2010, 08:28 AM
pauljonze
Couldn't you just use a hidden column to equate to one when a=0 and b=x then summate it?
• 14th May 2010, 08:34 AM
elsiegee40
Quote:

Originally Posted by pauljonze
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! :redface:
• 14th May 2010, 08:39 AM
pauljonze
Oops! Sorry - didn't read previous post! Looks like a macro then?
• 14th May 2010, 09:14 AM
SYSMAN_MK
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.
• 14th May 2010, 09:29 AM
andy_b
Could also use Sumproduct:

=SUMPRODUCT((A1:A8=0)*(B1:B8="x"))
• 14th May 2010, 09:35 AM
elsiegee40
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, 08:10 AM
MattMitchell
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!
• 17th May 2010, 08:23 AM
gskelton
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