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. ## 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?

2. Use a nested COUNT function in an IF function?

Or COUNTIF?

3. 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. Sum If Function

Excel: SumIf Function

?????

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.

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

7. 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.

8. Couldn't you just use a hidden column to equate to one when a=0 and b=x then summate it?

9. 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!

10. Oops! Sorry - didn't read previous post! Looks like a macro then?

11. 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.

12. ## Thanks to SYSMAN_MK from:

elsiegee40 (14th May 2010)

13. Could also use Sumproduct:

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

14. ## Thanks to andy_b from:

elsiegee40 (14th May 2010)

15. 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. 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. 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: