Counting a specific number only if an adjacent cell has something

H

Hugsie Bear

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
 
D

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

Jonathan

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
 
H

Hugsie Bear

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
 
D

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