How do I use countif like sumif ie (range,criteria,count_range)
 

I need to count cells with values by reference to a range in the same way
that sumif sums them ie countif(a1:a10,a40,c1:c10)
This doesn't work but I'm hoping there's another way?

cheers
thanks

How do I use countif like sumif ie (range,criteria,count_range)
 

Your question is not entirely clear, but I think you may be looking for
this...

=SUMPRODUCT((A1:A10=A40)*(C1:C10<""))

--
Rick (MVP - Excel)


"tmarie" wrote in message
...
I need to count cells with values by reference to a range in the same way
that sumif sums them ie countif(a1:a10,a40,c1:c10)
This doesn't work but I'm hoping there's another way?

cheers
thanks

How do I use countif like sumif ie (range,criteria,count_range)
 

Explain *in words* what you want this formula to do:

countif(a1:a10,a40,c1:c10)

--
Biff
Microsoft Excel MVP


"tmarie" wrote in message
...
I need to count cells with values by reference to a range in the same way
that sumif sums them ie countif(a1:a10,a40,c1:c10)
This doesn't work but I'm hoping there's another way?

cheers
thanks

How do I use countif like sumif ie (range,criteria,count_range)
 

What are you counting in Col C?
There reason there is no third parameter in COUNTIF is that it is not
logically required...

You can count the values in A1:A10 if they are equal to the value in A40.
If you want to count values in C1:C10 which are non blank etc... then you
can use
COUNTA etc...


"tmarie" wrote:

I need to count cells with values by reference to a range in the same way
that sumif sums them ie countif(a1:a10,a40,c1:c10)
This doesn't work but I'm hoping there's another way?

cheers
thanks

How do I use countif like sumif ie (range,criteria,count_range)
 

Sorry but it does not make any sence for me, coutif(a1:a10,a40) should return
the same result as it:

=sumproduct(--(a1:a10=a40)*(c1:c10<""))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"tmarie" escreveu:

I need to count cells with values by reference to a range in the same way
that sumif sums them ie countif(a1:a10,a40,c1:c10)
This doesn't work but I'm hoping there's another way?

cheers
thanks

How do I use countif like sumif ie (range,criteria,count_range)
 

Hi,

The syntax of the COUNTIF() is different from that of SUMIF(). The
COUNTIF() function takes 2 inouts - range and criteria only. In the
SUMIF(), you can (optional) supply the sum range as well. Please read up on
COUNTIF() in the Help menu.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"tmarie" wrote in message
...
I need to count cells with values by reference to a range in the same way
that sumif sums them ie countif(a1:a10,a40,c1:c10)
This doesn't work but I'm hoping there's another way?

cheers
thanks