Countif question how do I do this ?

[pano]

Hi everyone,
Help is needed.........

cells B5 to B40 may contain a number or be left blank--- cells O5 to
O40 may contain a number or be left blank.

I need to count the instances where a number appears in a cell B5 to
B40 and cells O5 to O40 example

cell B10 cell O10 cell z10
3 1 = 1 instance

cell B15 cell O15
7 1 = 1
instance so total would be 2 instances of criteria met.

Hope this is clear to someone who could help me with this.

Thanks in advance......

 

[Ron Coderre]

Try something like this:

=SUMPRODUCT(ISNUMBER(B5:B40)*ISNUMBER(O5:O40))


Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[pano]

Try something like this:

=SUMPRODUCT(ISNUMBER(B5:B40)*ISNUMBER(O5:O40))

Does that help?
***********
Regards,
Ron

XL2002, WinXP











- Show quoted text -

Ron thanks for the input but no it does not count the instances for me
when I put your formula in it just comes up blank

 

[Ron Coderre]

I posted a bit of a shortcut....the formula should automatically count
instances where same-row cells in Col_B and Col_O contain numbers.

Consequently, you wouldn't need to list it in Col_Z (if that's what you're
doing)
If this was the only activity:
Refs Col_B Col_O
Row_5 1 (blank)
Row_6 (blank) (blank)
Row_7 (blank) 3
Row_8 4 5
Row_9 9 8

The formula would return: 2 (OnlyRow 8 and 9 contain 2 number)

Is there a problem with the data? numeric text, possibly?

Try this test:
A5: =ISNUMBER(B5)
Copy that formula down through A40

The formula will return TRUE for every referenced cell that contains a number.
Repeat for O5:O40

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[pano]

Ron thanks for the input but no it does not count the instances for me
when I put your formula in it just comes up blank- Hide quoted text -

- Show quoted text -

I have come up with this but it still counts cell O10 when it has a
value (1) as an instance when cell B10 is empty

=SUM(IF((B5:B40>0)+(O5:O40>0),1,0))
so there must be something wrong with this and I bet it is the + sign
in the middle

Something like this SUMIF B5:B40 has a number greater than "" and
O5:O40 has a corrosponding number greater than "" then count as 1
instance

 

[pano]

I posted a bit of a shortcut....the formula should automatically count
instances where same-row cells in Col_B and Col_O contain numbers.

Consequently, you wouldn't need to list it in Col_Z (if that's what you're
doing)
If this was the only activity:
Refs Col_B Col_O
Row_5 1 (blank)
Row_6 (blank) (blank)
Row_7 (blank) 3
Row_8 4 5
Row_9 9 8

The formula would return: 2 (OnlyRow 8 and 9 contain 2 number)

Is there a problem with the data? numeric text, possibly?

Try this test:
A5: =ISNUMBER(B5)
Copy that formula down through A40

The formula will return TRUE for every referenced cell that contains a number.
Repeat for O5:O40

Does that help?
***********
Regards,
Ron

XL2002, WinXP





- Show quoted text -

Ok Ron I tried your formula on a new blank sheet and yes it works so I
dont understand why it wont work on my workbook. I ran your test
B5:B40 came up false in all cells and O5:O40 came up true in all
cells---O5:O40 does have a formula in each cell which is as follows
=(COUNTIF(COUNT!C42:C72,J5)>0)+0 whereas B5 to B40 the user has to
enter a number.

Awaiting your further advice to helpo solve this if indeed it can be??

 

[Ron Coderre]

Make sure B5:B40 is NOT formatted as TEXT.

Select B5:B40...then
From the Excel main menu:
<format><cells><number tab>
Category: (Select "General" or "Number"...anything but "Text")

Note: if they were set as Text, you'll need to coerce their values from text
to numbers.....
Select B5:B40
<data><text-to-columns>....click the [finish] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[pano]

Ok Ron I tried your formula on a new blank sheet and yes it works so I
dont understand why it wont work on my workbook. I ran your test
B5:B40 came up false in all cells and O5:O40 came up true in all
cells---O5:O40 does have a formula in each cell which is as follows
=(COUNTIF(COUNT!C42:C72,J5)>0)+0 whereas B5 to B40 the user has to
enter a number.

Awaiting your further advice to helpo solve this if indeed it can be??- Hide quoted text -

- Show quoted text -

OK Ron thanks for your help but I have solved it with this formula and
I just count up the rows....to get the total

=SUM(IF((B5:B40>0)*(O5:O40>0),1,0))

 

[Ron Coderre]

OK....but be aware that any cell that contains text will be treated as a
number.

Example:
B10: Hello
O10: there

Your formula will count that as a double number combination.

***********
Regards,
Ron

XL2002, WinXP