Forum Home
Press F1
 
Thread ID: 93972 2008-10-08 21:03:00 Spreadsheet Function dveitch (6045) Press F1
Post ID Timestamp Content User
710879 2008-10-08 21:03:00 There may be a simple answer to this, but I have lost it.

What is the equation or function? If I have cells in column A which are blank or have numbers 0, 1, 2. And in column B I have numbers 1, 2, 3. Then I want to count the rows that have a 1 in column B and a blank in column A.

If there is no function then I will have to puddle throught the entire spreadsheet and put something in the blank cells of column a.

Thanks

Dick 		dveitch (6045)
710880 2008-10-08 21:31:00 Hi Dick - give the COUNTIF function a try.

HTH 		nofam (9009)
710881 2008-10-09 00:30:00 What is the equation or function? If I have cells in column A which are blank or have numbers 0, 1, 2. And in column B I have numbers 1, 2, 3. Then I want to count the rows that have a 1 in column B and a blank in column A.



Copy this down Column C.

=IF(AND( ISBLANK(A1), B1=1 ),0,1)

In the next lowest Cell, either Add or Count the ones.

Something like that?

For variety, try this.
=IF(AND( ISBLANK(A1), B1=1 ),"Bugger","Blessing")

Then Count your Blessings.

(The devil made me do it.)

Cheers,
MistyCat 		MistyCat (11583)
710882 2008-10-09 18:40:00 Thanks, folks. I can use COUNTIF to get a count for a single column but I thought that I could simultaneously check the adjacent column something like:

=Countif((Y3:Y1543,"=1")+AND(X3:X1543,isblank))

But I just get that rude noise and error message. Maybe it is just a comma in the wrong place.

Dick 		dveitch (6045)
710883 2008-10-10 00:01:00 Thanks, folks. I can use COUNTIF to get a count for a single column but I thought that I could simultaneously check the adjacent column something like:

=Countif((Y3:Y1543,"=1")+AND(X3:X1543,isblank))

But I just get that rude noise and error message. Maybe it is just a comma in the wrong place.

Dick

You need an array formula, try this formula

=COUNT(IF((Y3:Y1543=1)*(X3:X1543=""),))

but enter using <Ctrl><Shift><Enter> to make it an array formula

so it appears as

{=COUNT(IF((Y3:Y1543=1)*(X3:X1543=""),))}

HTH 		rad_s4 (7401)
710884 2008-10-10 07:53:00 Thanks, that works wonderfully.

Dick 		dveitch (6045)
1