Hi,

I am trying to work out how to use if and count if to count the number of
cells with a certain number in, but only if a cell in an adjacent coloumn has
something in it. If statements don't seem to take ranges for its logical
test, and countif doesn't seem to want to allo me to define the criteria as,
cell=1 AND adjacent cell < 0

My data is something like

1 100
1
1 15
2
2 67
1 34
3 82
3
2
3 43

I want to get a readout of the number of 1's that have something in adjacent
coloumn.

Thanks in advance

Hugsie

Try

=SUMPRODUCT(--(A1:A100=1),--(B1:B100<""))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=567420

where col g is the adjacent cell
count non blanks in g
=COUNTIF(G1:G21,"<")
sum g for 1 in col f
=SUMPRODUCT(--(F1:F21=1),--G1:G21)
count f for numbers in g
=SUMPRODUCT(--(F1:F21=1),--ISNUMBER(G1:G21))
--
Don Guillett
SalesAid Software

"Hugsie Bear" wrote in message
...
Hi,

I am trying to work out how to use if and count if to count the number of
cells with a certain number in, but only if a cell in an adjacent coloumn
has
something in it. If statements don't seem to take ranges for its logical
test, and countif doesn't seem to want to allo me to define the criteria
as,
cell=1 AND adjacent cell < 0

My data is something like

1 100
1
1 15
2
2 67
1 34
3 82
3
2
3 43

I want to get a readout of the number of 1's that have something in
adjacent
coloumn.

Thanks in advance

Hugsie

How about making a new column with the following:

if(and(a1=1,not(isblank(b1))),1,0)

and then just get the sum of this column?

Cheers,
Jonathan

"Hugsie Bear" wrote:

Hi,

I am trying to work out how to use if and count if to count the number of
cells with a certain number in, but only if a cell in an adjacent coloumn has
something in it. If statements don't seem to take ranges for its logical
test, and countif doesn't seem to want to allo me to define the criteria as,
cell=1 AND adjacent cell < 0

My data is something like

1 100
1
1 15
2
2 67
1 34
3 82
3
2
3 43

I want to get a readout of the number of 1's that have something in adjacent
coloumn.

Thanks in advance

Hugsie

Thanks daddy,

I worked it out with some lateral thinking and used

{=sum(if(range1=1, if(range2 <"" , 1 , 0) ) ) } which seemed to work

thanks for your help

Hugsie
"daddylonglegs" wrote:


Try

=SUMPRODUCT(--(A1:A100=1),--(B1:B100<""))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=567420

OK, but I'd suggest that SUMPRODUCT is a tad simpler, it doesn't require
CTRL+SHIFT+ENTER for example.

If you want to use an array formula you can shorten to

{=sum((range1=1)*(range2 <""))}


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=567420