Countif?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Helen - 07 Mar 2007 20:23 GMT

(please forgive me if this is a duplicate posting...)

I'm trying to do the following:

Count how many cells (rows) in column A that has the value 1 in Column C AND
value 1 in Column D?

Any suggestions?

Helen

Reply to this Message

Ragdyer - 07 Mar 2007 20:44 GMT

So ... what does Column A have to do with the count?
=Sumproduct((C1:C100=1)*(D1:D100=1))

Do you want to check if the cell in Column A contains *anything?
=Sumproduct((C1:C100=1)*(D1:D100=1)*(A1:A100<>""))

OR ... if Column A is *not* equal to 0?
=Sumproduct((C1:C100=1)*(D1:D100=1)*(A1:A100>0))

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> (please forgive me if this is a duplicate posting...)
>
[quoted text clipped - 6 lines]
>
> Helen

Reply to this Message

Sandy Mann - 07 Mar 2007 21:50 GMT

If this is a real problem not just an example, (ie that there really will be
1's used), then a simple SUMIF() should do it:

=SUMIF(A1:A10,1,C1:C10)

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> So ... what does Column A have to do with the count?
> =Sumproduct((C1:C100=1)*(D1:D100=1))
[quoted text clipped - 15 lines]
>>
>> Helen

Reply to this Message

Ragdyer - 08 Mar 2007 04:59 GMT

What happens when Column C contains a 1, and Column D contains a 2 and/or a
3?

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> If this is a real problem not just an example, (ie that there really will be
> 1's used), then a simple SUMIF() should do it:
[quoted text clipped - 24 lines]
> >>
> >> Helen

Reply to this Message

Pete_UK - 07 Mar 2007 20:45 GMT

Helen,

You can only use COUNTIF (and SUMIF) when you have one condition - for
multiple conditions you need to use SUMPRODUCT, like so:

=SUMPRODUCT((C1:C100=1)*(D1:D100=1))

if you just want to count them, or this:

=SUMPRODUCT((C1:C100=1)*(D1:D100=1)*(A1:A100))

if you want to add them up.

Hope this helps.

Pete

> (please forgive me if this is a duplicate posting...)
>
[quoted text clipped - 6 lines]
>
> Helen

Reply to this Message