Counting a specific number only if an adjacent cell has something

[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

[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))
--
Don Guillett
SalesAid Software
[email protected]
"Hugsie Bear" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"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

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

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