Counting a specific number only if an adjacent cell has something

[Guest]

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

 

[daddylonglegs]

Try

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

 

[Don Guillett]

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

 

[Guest]

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

 

[Guest]

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]

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 <>""))}