[ms office - 2003] Excel expert required - Help please

[elsiegee40]

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?

[tech_guy]

Use a nested COUNT function in an IF function?

 

Or COUNTIF?

 

LINK

Edited May 13, 2010 by tech_guy
I'm drunk & can't really think staright....

[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),"")"

[mac_shinobi]

Sum If Function

 

Excel: SumIf Function

 

?????

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

example double countif.xls

[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

[elsiegee40]

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.

[pauljonze]

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

[elsiegee40]

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!

[pauljonze]

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

[andy_b]

Could also use Sumproduct:

 

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

[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!

[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!

[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